Vlookup - reading multiple rows

[mirandapanda]

Main Report:
I have my main booking references in column A, secondary supplier booking references in column B, amount due in column C.

Fund Report:
I have my main booking reference in this report.

For each main reference, there can be 1 secondary supplier ref, or multiple. If my main booking reference has more than 1 secondary supplier booking ref, it shows on separate rows. What I want to do is somehow collate the 2, so that when I have my funds report that shows which bookings to look at, I want to somehow vlookup or something my main ref off my fund report, to show each secondary supplier booking reference and the amount due on that row.

Any ideas?

Thanks
Miranda

 

[AlanY]

Re: Help with vlookup - reading multiple rows

how about?

Excel 2013/2016
	A	B	C	D	E	F	G
1	ref	supplier	amount		ref	supplier	amount
2	A	1000	700		C	1000	200
3	C	1000	200			1002	500
4	A	1001	600			1004	500
5	B	1002	500			1006	600
6	C	1002	500
7	D	1002	100
8	A	1003	300
9	B	1003	900
10	C	1004	500
11	A	1005	700
12	D	1005	300
13	C	1006	600
14	A	1007	800
15	B	1008	500
16	A	1009	100
17	A	1010	200
Report

Cell Formulas
Range	Formula
F2	{=IF(COUNTIF($A$2:$A$17,$E$2)>=ROWS($F$2:F2),INDEX($B$2:$B$17,SMALL(IF($A$2:$A$17=$E$2,ROW($A$2:$A$17)-ROW($A$2)+1),ROWS($F$2:F2))),"")}
G2	{=IF(COUNTIF($A$2:$A$17,$E$2)>=ROWS($F$2:G2),INDEX($C$2:$C$17,SMALL(IF($A$2:$A$17=$E$2,ROW($A$2:$A$17)-ROW($A$2)+1),ROWS($F$2:G2))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.