VLOOKUP
Posted by Brian on November 13, 2001 5:05 PM
Aloha,
Can you have a three column table using VLOOKUP? Using two columns to get an answer from the third?
Brian
Posted by Bob Umlas on November 13, 2001 7:39 PM
VLOOKUP will always search the first column only of a particular range and return a value from some column to the right. To use 2 items as a type of index value to get something from another column you need another formula, like an array formula, something like
=INDEX(C:C,MATCH(F3&F4,B1:B50&C1:C50,0))
which needs to be ctrl/shift/entered, and assumes there are items in F3 & F4 which appear in B1:B50 and C1:C50.
Example: F3 contains Apple & F4 contains "Fruit".
Somewhere in B1:B50 is "Apple", maybe several times, and somewhere in C1:C50 is "Fruit", also maybe several times, but only one time are they on the same row, and that's where you want to take the item from column C. The above formula accomplishes that.
Is that what you want?
Posted by Brian on November 13, 2001 10:27 PM
Aloha Bob,
I'll try this at work tomorrow!
Mahalo...
Brian
Posted by Brian on November 13, 2001 10:46 PM
=INDEX
Bob,
I just ran a fast test on this formula. I have three columns. I have a number which appears in cell I16 with also appears in column D. I also have a number which also appears cell M16 which appears in column E. Much like plotting a graph, a value of both cells appears in column F. Confused yet!!!! I want that value to appear in cell P16.
Brian
