Index/Match Confusion

[vxs]

Hi all,
I don't know why I struggled with Index / Match so much, or whether it is the right formula for the job but can you assist please?

I have a table which I am trying to total up some of the relating values elsewhere by the persons name. Example Data

Part	Amount	Paid By
Oil Cap	£50	Tom
Exhaust	£100	Tom
Bumper	£200	George
Wing	£300	James
Bonnet	£100	George

So in the other table I have a column for each person and I wish to list the amounts they have paid.

Tom	George	James
£50	£200	£300
£100	£100

Because the lookup is using the person's Name and looking Left for info I decided I couldn't use vlookup and needed to use Index/Match. But every time I use index match I just get the first result and don't then know how to move on to the 2nd, 3rd or 4th result if there is one to match and display and duplicate the formulas going down.

Can anyone advise please?

 

[mole999]

when learning INDEX MATCH, try just doing the first part as MATCH only which will give you the ROW number, adding the INDEX later can help to understand it

as for VLOOKUP it is designed to do the First Result from a sorted list, also VLOOKUP looks at the leftmost column where INDEX/MATCH can be transposed

 

[DanteAmor]

In this case you need another function instead of Match, since it would only bring you the first match and you need to get several.

This is an option:

Dante Amor
	A	B	C	D	E	F	G	H
1	Part	Amount	Paid By			Tom	George	James
2	Oil Cap	£50	Tom			£50	£200	£300
3	Exhaust	£100	Tom			£100	£100
4	Bumper	£200	George
5	Wing	£300	James
6	Bonnet	£100	George
7
Hoja4

Cell Formulas
Range	Formula
F2:H6	F2	=IFERROR(INDEX($B$2:$B$6,SMALL(IF($C$2:$C$6=F$1,ROW($C$2:$C$6)),ROWS(G$2:G2))-1),"")
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[vxs]

In this case you need another function instead of Match, since it would only bring you the first match and you need to get several.

This is an option:

Dante Amor
	A	B	C	D	E	F	G	H
1	Part	Amount	Paid By			Tom	George	James
2	Oil Cap	£50	Tom			£50	£200	£300
3	Exhaust	£100	Tom			£100	£100
4	Bumper	£200	George
5	Wing	£300	James
6	Bonnet	£100	George
7
Hoja4

Cell Formulas
Range	Formula
F2:H6	F2	=IFERROR(INDEX($B$2:$B$6,SMALL(IF($C$2:$C$6=F$1,ROW($C$2:$C$6)),ROWS(G$2:G2))-1),"")
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

I will try this formula later when back on the PC, thank you for submitting a working formula to go by which is editable to suit my needs.

 

[AhoyNC]

Here are a couple other options depending on the version of Excel you have.
If you have Office 365 then you can just use the FILTER function as in cell A10 in the example below.
If you have Excel 2010 or later ten the formula in A15. Copy down and across. This is basically the option DanteAmor gave you, but it does not require CTL-SHIFT-ENTER, just Enter.

Book1
	A	B	C
1	Part	Amount	Paid By
2	Oil Cap	£50	Tom
3	Exhaust	£100	Tom
4	Bumper	£200	George
5	Wing	£300	James
6	Bonnet	£100	George
7
8
9	Tom	George	James
10	£50	£200	£300
11	£100	£100
12
13
14	Tom	George	James
15	£50	£200	£300
16	£100	£100
Sheet1

Cell Formulas
Range	Formula
A10:C10	A10	=FILTER($B$2:$B$6,$C$2:$C$6=A$9)
A15:C16	A15	=IFERROR(INDEX($B$2:$B$6,AGGREGATE(15,6,(ROW($B$2:$B$6)-ROW($B$2)+1)/($C$2:$C$6=A$14),ROWS($A$15:A15))),"")