Vlookup and Multiple Ranges

If there is no risk of duplicate entries in the A:H range, and you don't have a massive amount of data to work with

=INDIRECT(ADDRESS(SUMPRODUCT(($A$2:$H$11=J3)*ROW($A$2:$H$11)),SUMPRODUCT(($A$2:$H$11=J3)*COLUMN($A$2:$H$11))+1))

Otherwise it would be something on the lines of

=IF(ISNA(VLOOKUP(J2,$A$2:$B$11,2,0)),IF(ISNA(VLOOKUP(J2,$D$2:$E$11,2,0)),IF(ISNA(VLOOKUP(J2,$G$2:$H$11,2,0)),0,VLOOKUP( J2,$G$2:$H$11,2,0)),VLOOKUP(J2,$D$2:$E$11,2,0)),VLOOKUP(J2,$A$2:$B$11,2,0))

Or (excel 07 or later)

=IFERROR(VLOOKUP(J2,$A$2:$B$11,2,0),IFERROR(VLOOKUP(J2,$D$2:$E$11,2,0),IFERROR(VLOOKUP(J2,$G$2:$H$11,2,0),0)))

Thanks Joson,
This helped me a lot!

Hi macah!t

Try this. For simplicity, assume your ranges are a2:b4,d2:e4,and g2:h4 respectively. Assume your results range begins at j2:k2 (goes downward). J2 is the lookup value, K2 is your where your formula to be copied down

in K2 =lookup(99^99,choose({1,2,3},vlookup(j2,a2:b4,2,false),vlookup(j2,d2:e4,2,false),vlookup(j2,g2:h4,2,false))). If you are solving for text entries, use =rept("z",255)..... instead of 99^99. Use $ to lock your ranges to absolute values.Copy down

Or in K2 =lookup(99^99,choose({1,2,3},index(b2:b4,match(j2,a2:a4,0)),index(e2:e4,match(j2,d2:d4,0)),index(h2:h4,match(j2,g2:g4,0)))). Lock in your index ranges and lookup ranges. Copy down
HTH,
Mike Szczesny

Try...

K2, just enter and copy down:

=LOOKUP(9.99999999999999E+307,
CHOOSE({1,2,3,4},
0,
VLOOKUP(J2,$A$2:$B$11,2,0),
VLOOKUP(J2,$D$2:$E$8,2,0),
VLOOKUP(J2,$G$2:$H$4,2,0)))
[/code]

Note 1. On European systems, replace the decimal dot in the big number with comma and use semi-colon between terms instead of comma.

Note 2. Custom format the result range in column K as [=0]"";General.