Function VLOOKUP

N

NSim

How do I get VLOOKUP to recognize a cell reference for lookup_value? It only works when I enter a number into the lookup_value.
 
J

J.E. McGimpsey

NSim said:
How do I get VLOOKUP to recognize a cell reference for lookup_value? It only
works when I enter a number into the lookup_value.
Click to expand...

If your lookup reference is in cell A1, simply enter the cell
reference:

=VLOOKUP(A1, table, 2, FALSE)

There are several things that can lead to VLOOKUP not finding the
values you think it should. For instance, if the lookup table is
numeric, but the lookup value is Text, the lookup will fail.

If the formula above doesn't help, post back with details on your
setup and what isn't working.
 
N

NSim

I have my formula set up VLOOKUP(A1,Table,2, False) and my table is column of Numbers (Area Codes) and a column of Words (cities). My LOOKUP_VALUE is a number(area code) and I want it to spit out the corresponding City. For some reason when LOOKUP_VALUE is a reference to a cell, for example A1, it gives me the #N/A error. However when I enter the area code directly into the formula, it gives me the right result. -- VLOOKUP(405,Table,2, False).

Any help would be much appreciated!
 
J

J.E. McGimpsey

NSim said:
I have my formula set up VLOOKUP(A1,Table,2, False) and my table is column of
Numbers (Area Codes) and a column of Words (cities). My LOOKUP_VALUE is a
number(area code) and I want it to spit out the corresponding City. For some
reason when LOOKUP_VALUE is a reference to a cell, for example A1, it gives
me the #N/A error. However when I enter the area code directly into the
formula, it gives me the right result. -- VLOOKUP(405,Table,2, False).
Click to expand...


Is A1 formatted as Text? or does it contain the result of a text
function (like LEFT(B1,3))?

If it's a value, rather than a function, try coercing the text to a
number value by setting A1's format to General or another number
format, copying an empty cell, selecting A1, then choosing
Edit/Paste Special, selecting the Values and Add radio buttons.

If it's a function (like =LEFT(B1,3)) the text can be coerced to a
number using double unary minus operators: =--LEFT(B1,3)
 
Top