Assistance with Excel Index's

GMHolder

New Member
Joined
Aug 20, 2018
Messages
2
Hi All,

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


Eddie 10
Steve12
Steve65
Steve12
Steve12
Steve35
Steve56
James66
James73
James21
James33
James21
James21
James56
James5
James5
James5
James78
James54
James54
James5
James43
James23
James43
James4
Paul54
Paul67

<colgroup><col><col></colgroup><tbody>
</tbody>


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

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,344
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.
 

Forum statistics

Threads
1,085,545
Messages
5,384,369
Members
401,889
Latest member
Pmccollin

Some videos you may like

This Week's Hot Topics

Top