1. Registered User
Join Date
Jan 2002
Location
Bay Area
Posts
513

I need to know if you can do a VLOOKUP from 2 cell references or find an alternate solution as this does not seem possible with the VLOOKUP function. For example, product # 1000 is used at branches CA_01, CA_20, FL_01, FL_20,TX_75
The branch is typed in sheet #1, cell A2 and product typed in B2. Sheet #2 has ranges named CA_01, CA_20, FL_01...etc. and each named range has the (same) product numbers as the key, and other columns that I want to look up.

Using "CA_01" in one cell (A2 in sheet #1) and 1000 (in B2) together in a VLOOKUP statement in C2 does not find anything in named range "CA_01"
The statement reads "=VLOOKUP(B2,A2,2,False)" "#N/A" is returned.

I am hoping to hear from the experts.

Thanks.
Jerry

2. Registered User
Join Date
Jul 2003
Posts
9

## Re: Complex VLOOKUP Question

Vllokup uses parameters -
The value that you are looking for in the first column of the lookup table

The Lookup table

The column number in the lookup table to be returned if a match is found

FALSE - an exact match is needed otherwise N/A is returned if TRUE then nearest above is returned

You seem to be searching for 1000 in the table 'A2' that contains the value CA_01. This will return N/a as they are not the same.
You've not said exactly what you are after as an end result ? Is it the names of the branches that 'use' product 1000 (or whatever product is selected) or are you trying to find out what barnches 'use' what produuct. Either way I suspect you will have multiple results as in the example for #1000 that you have given, in which case VLOOKUP is not the answer.
If however you are just trying to determine whether a branch 'uses' particular product you could concatenatet the two columns in your second worksheet giving you a single columns containing all possible combinations. VLOOKUP can the use a concatinated value A2&B2 against the lookup table's concatenated column.

Originally posted by JerryDal
I need to know if you can do a VLOOKUP from 2 cell references or find an alternate solution as this does not seem possible with the VLOOKUP function. For example, product # 1000 is used at branches CA_01, CA_20, FL_01, FL_20,TX_75
The branch is typed in sheet #1, cell A2 and product typed in B2. Sheet #2 has ranges named CA_01, CA_20, FL_01...etc. and each named range has the (same) product numbers as the key, and other columns that I want to look up.

Using "CA_01" in one cell (A2 in sheet #1) and 1000 (in B2) together in a VLOOKUP statement in C2 does not find anything in named range "CA_01"
The statement reads "=VLOOKUP(B2,A2,2,False)" "#N/A" is returned.

I am hoping to hear from the experts.

Thanks.
Jerry

3. Registered User
Join Date
Jan 2002
Location
Bay Area
Posts
513
Thanks for your good advice. Using this technique, for example, "=VLOOKUP(\$A1&\$A2,MYTABLE,2,False)" and having a concatenated column as the key in my lookup table, is a workable solution.

Jerry

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•