double vlookup in a range

jjst34 · Oct 25th 2004

I'd like to do a vlookup like this...
first find "smith"
then once smith is found look in the the range 4 columns over (column E) and a range of 10 rows down, and look for "cross", then return the value 1 column over from cross.
Is this possible?

basically smith will be found in say cell A324, then I want it to look in cells E324:E33 for "cross", lets say found in E329, then return the value in F329.

Cross is not always in the same position in relation to smith, that's why I can't use an offset in the code. Thanks!

Anonymous · Oct 25th 2004

As long as you do not put the formula in column A (or if you modify the formula to exclude the column A cell in which you put the formula) the following should work. (See B1 in the attached file.)

=VLOOKUP("cross",OFFSET(A1,MATCH("Smith",A:A,1)-1,4,10,2),2,FALSE)

Will Riley · Oct 25th 2004

You just need to specify your ranges - I would not use the whole column

=VLOOKUP("cross",OFFSET(A1,MATCH("smith",$A$1:$A$30,0),5,10,2),2,FALSE)

I got this to work

Edit: I see Tom got there first - not sure who's right with the columns...we seem to differ

jjst34 · Oct 26th 2004

I appreciate your help guys.. works great!

double vlookup in a range

Participate now!

Share