VLookup Cell Reference to Table Name
Posted by Marc on April 17, 2001 3:27 PM
Is it possible to refer to a cell rather than a specific table when using vlookup? For example, the typical vlookup might look like:
=vlookup(lookupValue,Table,Column)
I want to do something like:
=vlookup(lookupValue,A5,Column)
In this case, A5 might be equal to "Table"...Unfortunately when I try, the A5 comes back as a text and the formula fails (#N/A)
Posted by Dave Hawley on April 17, 2001 3:34 PM
Hi Marc
Use the INDIRECT function:
=VLOOKUP(Lookup_Value,INDIRECT(A5),Col,FALSE)
Dave
OzGrid Business Applications
Posted by Aladin Akyurek on April 18, 2001 3:11 AM
Hi Marc
I guess you found the answer that I gave unsatisfactory at 14791.html
This is another try.
Your formula
=vlookup(lookupValue,Table,Column)
suggests that you have in A5 a name (Table, perhaps) referring to a range that must be used as lookup table.
Vlookup needs ordinarily 4 arguments. The 4th arg consists of either FALSE or TRUE (equivalently, either 0 or 1). If you omit the 4th arg (as you have done), the value thereof defaults to TRUE.
TRUE (or 1) means approximate match, FALSE (or 0) exact match.
Your formula gives #N/A already because of A5 which is not a table. If A5 contains a range such as E5:F25 or a name such as Table that refers to a range, you really need to use INDIRECT.
=VLOOKUP(lookup-value,INDIRECT(A5),lookup-column, either-FALSE-or-TRUE)
You can still get an #N/A, but that's ok meaning that the lookup-value is not available in the first column of your lookup-table. If you want to avoid seeing #N/A at all costs, try the following.
Select the first column of your lookup table and give it an appropriate name via the Name Box. Place that name, say, in A6. And use the following formula:
=IF(ISNUMBER(MATCH(lookup-value,INDIRECT(A6),either-0-or-1),VLOOKUP(lookup-value,INDIRECT(A5),lookup-column,either-0-or-1),a-return-value-if-no-match)
where the choice of either-0-or-1 must be the same for MATCH and VLOOKUP and a-return-value-if-no-match can be anything: such as "",0,"Not Found",etc.
Hope this helps.
Aladin
