Excel - Index Match Producing Same Values

Question

Hi All,

Got a slight issue with a report I am creating.  I am using an Match nested within an Index formula to identify specific values against the user who produced the values.  However, some of the users have not produced any values and this equates to £0.00 in the cell list.

When using the Index formula it will basically read the values in descending order, with the highest at the top, and then match against the user who produced it.  When we the formula reaches the first of the £0.00 values, it will repeat the same name over and over again until the formula range has ended.  Everything else on the formula works fine, but I just need to ensure that the remaining £0.00s are producing one of the other names next to it.

Please see example below.

Display Sheet:

John Smith - £345

Adam Gallaway - £248

Jack Richards - £244

Rachel Harrods - £198

Gavin Roberts - £0

Gavin Roberts - £0

Gavin Roberts - £0

Data Source:

Aimee Louise - £0

Hannah Short - £0

Jack Richards - £244

Rachel Harrods - £198

Gavin Roberts - £0

John Smith - £345

Adam Gallaway - £248

Answer 1

Are you OK to use a helper column in Source Sheet. If yes, the problem will be very simple

1. Put following formula in Data Source say in cell C2 and drag down

=RANK(B2,$B$2:$B$8)+COUNTIF(B$2:B2,B2)-1

2. Put following formula in A2 of Summary Sheet and drag right and down

=INDEX('Data Source'!A$2:A$8,MATCH(ROW($A1),'Data Source'!$C$2:$C$8,0))

If you are not willing to use Helper column, let me know.

Answer 2

Hi,

My organisation set up is preventing sharing outside of our business.  As you can see how the data in being built in the Excel document.  Would it be possible for you to create a Data Source sheet, and then use the Formulas I have set below to make your own version of this document?

Formula for Column A: 

=INDEX('Data Source'!A:A,MATCH(B2,'Data Source'!B:B,0))

Formula for Column B:

=LARGE(DataAmount,C2)

'DataAmount' is the a range that has been applied to only the Numerical Values in the DataSource sheet (column B).

I hope this helps.

Kind regards,

Jack Charles

Answer 3

I am not authorized to this file. Hence, can't download.

You can upload from Onedrive application on your computer.

If you don't have this application,

1. Login to https://onedrive.live.com/ utilizing the same Login ID and Password which you have used on this forum. (or use the installed Onedrive on your computer)
2. Click Upload in the top OR drag and drop the file here.
3. After uploading, right click the file and choose share.
4. Optional but recommended - Uncheck the Allow Editing
5. Click Get a Link.
6. Copy the link and paste the link here.

Answer 4

Hi,

I have created a dummy template and uploaded it to OneDrive.  Please see the link below.

OneDrive File

This template is using the exact same formulas and set up as my live version, minus the confidential information.  As you will see, on the 'Summary Sheet', the final value of £0.00 has the same user listed three times.  What I need is for the formula to list the other two users below the first.  

Kind regards,

Jack Charles

Answer 5

I need to have a look on your workbook to provide you the right solution.

Request you to please prepare and upload a sample / dummy file sans confidential / sensitive data to Onedrive and share the link? It will help me to give prompt and high quality solution.