Move the value from a +vlook(... cell to another cell

[SRRchdsn]

I have a cell which I populated via a vlookup. Is there a formula which will move the "value" from the vlookup cell to another cell.

 

[crook_101]

Hi & Welcome to the Board,

Do you mean that you want the value that you have "looked up", ie the value that is in the cell =vlookup(etc....) to be shown in a different cell??

Cheers,
Ian

 

[SRRchdsn]

Yes

 

[crook_101]

Hi,

Think the below shows you how:

Excel 2007<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0; TEXT-ALIGN: center"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH></TR></THEAD><TBODY><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">1</TD><TD>Column 1</TD><TD>Column 2</TD><TD style="TEXT-ALIGN: right"></TD><TD>Lookup</TD><TD style="TEXT-ALIGN: right"></TD><TD>Copy</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: right">1</TD><TD>A</TD><TD style="TEXT-ALIGN: right"></TD><TD>A</TD><TD style="TEXT-ALIGN: right"></TD><TD>A</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: right">2</TD><TD>B</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: right">3</TD><TD>C</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: right">4</TD><TD>D</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: right">5</TD><TD>F</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR></TBODY></TABLE>

Sheet2​

<TABLE style="BORDER-RIGHT: black 2px solid; PADDING-RIGHT: 0.4em; BORDER-TOP: black 2px solid; PADDING-LEFT: 0.4em; PADDING-BOTTOM: 0.4em; BORDER-LEFT: black 2px solid; PADDING-TOP: 0.4em; BORDER-BOTTOM: black 2px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all width="85%"><TBODY><TR><TD style="PADDING-RIGHT: 6px; PADDING-LEFT: 6px; PADDING-BOTTOM: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center" cellPadding=2 rules=all width="100%"><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0"><TH width=10>Cell</TH><TH style="PADDING-LEFT: 5px; TEXT-ALIGN: left">Formula</TH></TR></THEAD><TBODY><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>D2</TH><TD style="TEXT-ALIGN: left">=VLOOKUP(A2,A2:B6,2,0)</TD></TR><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>F2</TH><TD style="TEXT-ALIGN: left">=D2</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>


Cheers,
Ian

 

[Nalani]

To add to crook's sample data:

In case there is no value to Lookup in col A
You can use the follwing in D2 copied down

=IF(A2,VLOOKUP(A2,A2:B6,2,0),"")

This will give you the following view of the table presented by crook
Sheet1


<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 21px"><COL style="WIDTH: 64px"><COL style="WIDTH: 20px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Column 1</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Column 2</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Lookup</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="TEXT-ALIGN: center">Copy</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">1</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">A</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">A</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">A</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">2</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">B</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">B</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">B</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">C</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD></TR></TBODY></TABLE>


<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>D2</TD><TD>=IF(A2,VLOOKUP(A2,A2:B6,2,0),"")</TD></TR><TR><TD>F2</TD><TD>=D2</TD></TR><TR><TD>D3</TD><TD>=IF(A3,VLOOKUP(A3,A3:B7,2,0),"")</TD></TR><TR><TD>F3</TD><TD>=D3</TD></TR><TR><TD>D4</TD><TD>=IF(A4,VLOOKUP(A4,A4:B8,2,0),"")</TD></TR><TR><TD>F4</TD><TD>=D4</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>


Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

The reason I posted this formula is because if you use the formula posted by crook and there is no info in Col A, you will get an #N/A. Something I think you do not want.

Hope it helps

 

[SRRchdsn]

So, per you example(s), the actual value that is in F2 is not "A" but "=D2". What I am after is for the F2 cell to contain an "A".

 

[SRRchdsn]

So, per your example, the actual value that is in F2 is not "A" but "=D2". What I am after is for the F2 cell to contain an "A".