# Vlookup and Multiple Ranges

This is a discussion on Vlookup and Multiple Ranges within the Excel Questions forums, part of the Question Forums category; ...

1. ## Vlookup and Multiple Ranges

Hi all

Does any of you know of a formula for column "K" that finds the corresponding amount from the ranges A:b, D:E or G:H?

Sheet1
ABCDEFGHIJK
1test1test2test3
2fec € -75,00 vgrythggw € -135,00 fec € -75,00
3nhyn € -41,00 gty € -400,00 hjytrjrnhyn € -41,00
4cfgbth € -12,34 hytgrf € -150,00 yhjicfgbth € -12,34
5hytryjnhyt € -54,99 frtghytryjnhyt € -54,99
6rvf € -89,00 rdetgrvf € -89,00
7rw € -559,07 huygtrw € -559,07
8thy € -30,00 ui7juthy € -30,00
9btrer € -12,83 btrer € -12,83
10bnnj € -29,50 bnnj € -29,50
11uygvrw € -56,00 uygvrw € -56,00
12vgr
13gty € -400,00
14hytgrf € -150,00
15frtg
16rdetg
17huygt
18ui7ju
19ythggw € -135,00
20hjytrjr
21yhji

2. ## Re: 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

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)))

3. ## Re: Vlookup and Multiple Ranges

Thanks Joson,
This helped me a lot!

4. ## Re: Vlookup and Multiple Ranges

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

5. ## Re: Vlookup and Multiple Ranges

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.

#### Posting Permissions

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