V-Lookup Functionality
Posted by Bill Witowski on August 16, 2001 8:34 AM
How do I create a V-Lookup that will go to a table in another excel workbook and search on multiple variables. For example, I would like to search my vendors for the following types of variables.
Manufacturer Name in the first column
Product in the second column,
Model Number in the third column,
In my primary document (the one I want to pull information into) I have a list of Manufacturers with their Products and Models in first three columns. In the other document I have a table with the same three columns plus a fourth column that has a percentage number. I would like to be able to make my V-Lookup go to the table and pull in the data from the fourth column whenever I have an exact match with the first three columns in my primary document.
Please Help!
Thanks!
Posted by Mark W. on August 16, 2001 8:42 AM
Since VLOOKUP can only search the leftmost
column of the Lookup_array, create a new, leftmost
column consisting of the concatenation of these
values.If the values are varible length it would
be advisable to use a delimiter such as a comma.
For example...
='Manufaturer Name'&","&Product&","&'Model Number'
Posted by neo on August 16, 2001 8:53 AM
the following code uses a vlookup to check each column against the appropriate column in the 2nd book. if the vlookup doesn't find a match ( =if(isna(... ), then it returns nothing as an answer. but, if all three columns offer a match, it returns the percentage in the fourth column on the 2nd book. i think that's what you're looking for...?
=IF(ISNA(VLOOKUP(A2,[Book2]Sheet1!$A$2:$D$9,1,FALSE)),"1",IF(ISNA(VLOOKUP(B2,[Book2]Sheet1!$B$2:$D$9,2,FALSE)),"2",IF(ISNA(VLOOKUP(C2,[Book2]Sheet1!$C$2:$D$9,3,FALSE)),"3",VLOOKUP(A2,[Book2]Sheet1!$A$2:$D$9,4,FALSE))))
neo
Posted by Mark W. on August 16, 2001 9:07 AM
There's a serious problem...
...with your design. What if multiple manufacturers
produce the same Product?
Posted by neo on August 16, 2001 9:10 AM
Re: There's a serious problem...
so add a column that specifies the manufacturer name and check that one too...
neo
Posted by Mark W. on August 16, 2001 9:12 AM
Re: There's a serious problem...
I believe there already is such a column, but it
must be viewed in concurrence with Product and
perhaps Model. Hence the need for concatenation.
Posted by neo on August 16, 2001 9:38 AM
Re: There's a serious problem...
so the column's already there. one less step. just add a check for it in the formula. still don't see the "need for concatenation" unless you just fell like adding an extra formula...
neo
Posted by Mark W. on August 16, 2001 10:12 AM
Let's examine a test case...
Suppose Sheet1!A2:D3 contains...
{"Ford","Pickup","F150",0.1
;"Ford","SUV","Expedition",0.2}
So, look what happens with...
=IF(ISNA(VLOOKUP("Ford",Sheet1!$A$2:$D$9,1,FALSE)),"1",
IF(ISNA(VLOOKUP("SUV",Sheet1!$B$2:$D$9,2,FALSE)),"2",
IF(ISNA(VLOOKUP("Expedition",Sheet1!$C$2:$D$9,3,FALSE)),"3",
VLOOKUP("Ford",Sheet1!$A$2:$D$9,4,FALSE)
)
)
)
...it returns 10% rather than the expected 20%.
Posted by neo on August 16, 2001 10:34 AM
Re: Let's examine a test case...
that's because you butchered my formula by adding the "ford", "suv", etc. as text instead of cell references. i just recreated what you described above, and put the formula i wrote (not your altered version) in with complete success. concatenation who?
neo
Posted by neo on August 16, 2001 10:48 AM
Not to say yours would work fine...
i'm not saying your technique wouldn't work fine, i'm just saying there's no 'serious problem' with mine :-)
neo
Posted by Mark W. on August 16, 2001 11:20 AM
Not so...
My test used your formula...
=IF(ISNA(VLOOKUP(A2,Sheet1!$A$2:$D$9,1,FALSE)),"1",
IF(ISNA(VLOOKUP(B2,Sheet1!$B$2:$D$9,2,FALSE)),"2",
IF(ISNA(VLOOKUP(C2,Sheet1!$C$2:$D$9,3,FALSE)),"3",
VLOOKUP(A2,Sheet1!$A$2:$D$9,4,FALSE)
)
)
)
...and it does NOT work where...
A2 contains "Ford"
B2 contains "SUV"
C2 contains "Expedition"
...I substituted the text constants so you wouldn't
have to type them into A2:C2... So, have it your
way and type them yourself.
Also, take a minute to review what your formula
does...
1. If I can't find "Ford" return "1"
2. If I can't find "SUV" return "2"
3. If I can't find "Expedition return "3"
4. Else find the 1st occurance of "Ford" and
return its Percentage.
Unfortunately, it's not the 1st occurance of "Ford"
that were interested in. Hence my point you must
lookup the concurrence of these values...
="Ford"&","&"SUV"&","&"Expedition"
In database terminology the key to the percentage
table is a concatenated key...
='Manufacturer Name'&'Product'&'Model Number'
Posted by neo on August 16, 2001 12:26 PM
I bow...
i totally see it now. with the couple of products I was using in my test, it was workinf fine. but when i started mixing up stuff and adding several more entries...bugs (and not the bunny).
sorry about the stubborn persistance, but it's the only way i learn. this time you're the teacher:-) thanks
neo
Posted by Evan G. on August 17, 2001 12:11 PM
I think that the best way, given the other threads to this message is to create a hidden column in the "Source" workbook (call it Source.xls) that has a combination of the three variables assuming they are in columns a-c,
you would enter
=a2&b2&c2 to create a cell with all three.
Let's say it's in column d
(and let's assume the number you want is in column e -- the 2nd column in the range you'll be looking up)
Then, the vlookup in the "target" workbook (assuming the three columns are also a, b & c although this isn't necessary)
Vlookup(A2&b2&c2,[Source.xls]Sheet1!$d$2:$d$9,2,FALSE)
The false at the end ensures there is an exact match.
Also, if you want to make de-bugging much easier, use named ranges for your columns:
Select column A and call it Manufacturer, column b call product and column c modelnum
then the formula in Column D can simply be:
=manufacturer&product&modelnum
You can then call the range in column d that has the values you will be looking up -- Matchcode
The Vlookup can then be
Vlookup(A2&b2&c2,[Source.xls]Sheet1!Matchcode,2,FALSE)
