Assistance with Excel Index's

[GMHolder]

Hi All,

I was wondering if anyone could help me, i have a list of people, and payments.
for example :


[TABLE="width: 239"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Eddie [/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]Steve[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD]Steve[/TD]
[TD="align: right"]65[/TD]
[/TR]
[TR]
[TD]Steve[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD]Steve[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD]Steve[/TD]
[TD="align: right"]35[/TD]
[/TR]
[TR]
[TD]Steve[/TD]
[TD="align: right"]56[/TD]
[/TR]
[TR]
[TD]James[/TD]
[TD="align: right"]66[/TD]
[/TR]
[TR]
[TD]James[/TD]
[TD="align: right"]73[/TD]
[/TR]
[TR]
[TD]James[/TD]
[TD="align: right"]21[/TD]
[/TR]
[TR]
[TD]James[/TD]
[TD="align: right"]33[/TD]
[/TR]
[TR]
[TD]James[/TD]
[TD="align: right"]21[/TD]
[/TR]
[TR]
[TD]James[/TD]
[TD="align: right"]21[/TD]
[/TR]
[TR]
[TD]James[/TD]
[TD="align: right"]56[/TD]
[/TR]
[TR]
[TD]James[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]James[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]James[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]James[/TD]
[TD="align: right"]78[/TD]
[/TR]
[TR]
[TD]James[/TD]
[TD="align: right"]54[/TD]
[/TR]
[TR]
[TD]James[/TD]
[TD="align: right"]54[/TD]
[/TR]
[TR]
[TD]James[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]James[/TD]
[TD="align: right"]43[/TD]
[/TR]
[TR]
[TD]James[/TD]
[TD="align: right"]23[/TD]
[/TR]
[TR]
[TD]James[/TD]
[TD="align: right"]43[/TD]
[/TR]
[TR]
[TD]James[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Paul[/TD]
[TD="align: right"]54[/TD]
[/TR]
[TR]
[TD]Paul[/TD]
[TD="align: right"]67[/TD]
[/TR]
</tbody>[/TABLE]


I basically need to find a way that i can list all the values from one person in a separate list

I am using the following formula :

=IFERROR(INDEX(RawData!$B$1:$B$500,SMALL(IF(RawData!$A$1:$A$500="James",ROW(RawData!$A$1:$A$500)-ROW(RawData!$A$1)+1),ROWS(RawData!$A$1:$A500))),"")

Issue i am having is that i have a large number of blanks in my array $A$1:$A$500 in preparation for additional entries, and as a result i cannot obtain a full list of payments
i believe thats the SMALL function doing it but i'm not sure of a way round it,

it is doing my head in, could someone help
Gavin

 

[Marcelo Branco]

Welcome to Mr Excel forum

Your formula needs an adjustment. Try this

=IFERROR(INDEX(RawData!$B$1:$B$500,SMALL(IF(RawData!$A$1:$A$500="James",ROW(RawData!$A$1:$A$500)-ROW(RawData!$A$1)+1),ROWS(RawData!$A$1:$A1))),"")
Ctrl+Shift+Enter

M.

 

[GMHolder]

Fantastic thank you