Vlookup and Multiple Ranges

[mucah!t]

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?

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1	test1			test2			test3
2	fec	-75,00		vgr			ythggw	-135,00		fec	-75,00
3	nhyn	-41,00		gty	-400,00		hjytrjr			nhyn	-41,00
4	cfgbth	-12,34		hytgrf	-150,00		yhji			cfgbth	-12,34
5	hytryjnhyt	-54,99		frtg						hytryjnhyt	-54,99
6	rvf	-89,00		rdetg						rvf	-89,00
7	rw	-559,07		huygt						rw	-559,07
8	thy	-30,00		ui7ju						thy	-30,00
9	btrer	-12,83								btrer	-12,83
10	bnnj	-29,50								bnnj	-29,50
11	uygvrw	-56,00								uygvrw	-56,00
12										vgr
13										gty	-400,00
14										hytgrf	-150,00
15										frtg
16										rdetg
17										huygt
18										ui7ju
19										ythggw	-135,00
20										hjytrjr
21										yhji
Sheet1

 

[jasonb75]

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

 

[mucah!t]

Thanks Joson,
This helped me a lot!

 

[Mike Szczesny]

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

 

[Aladin Akyurek]

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.