Avoiding Duplicate Values when using INDEX/MATCH

Hi all, really hoping someone here can assist. I have a large dataset of weather observations, then have a Summary sheet that finds the top 10 values for each category. I then need to find the corresponding Station Name for that given top 10 value. My challenge is that there can be duplicate values in the top 10 list. When I use VLOOKUP or INDEX/MATCH to find the corresponding station, it only lists one station for all the duplicate values in my top ten list. How can I get it to find the next station that corresponds to the first duplicate value, instead of seemingly stalling at the first occurrence of that value it finds? I've tried sticking an IF function in there to compare the station it's computing to the previous station, but still can't figure out how to get it to kick down to the next station in the list that has that duplicate value. (all I can get it to return is a basic value like ND, for example)

In this example, the 24 actually corresponds to 4 different stations, instead of just the one... but once it hits Middle Lake alphabetically, it refuses to look further. The formula I'm using is

=INDEX(Analysis!$I:$I,MATCH(Summary!G7,Analysis!C:C,0))

I feel like I'm missing something really obvious here...

excel1.GIF

Thanks for the welcome! I suspect I'll be back with more questions as I have caught a bit of an Excel bug.

The helper column has worked like a charm, thank you GC Excel! And I've learned a new function (the ROW function), so a win all around! It took me a second to wrap my brain around what you were doing until I figured out what that ROW function did, haha!

Now if I could just get rid of the stupid lag in 2013, I'd be a happy camper...

Cheers!

Hi There,

I have a similar issue with my file. i have a list of Clients and it have fees attained by individuals for these clients. I have a slicer that allows you to select the names of these individuals and based on that I need a dynamic list of clients.
I have used the following formula:

{=IFERROR(INDEX(data!$A:$A,SMALL(IF(data!$D:$D='Summary 2'!$G$2,ROW(data!$A:$A)),ROW(1:1))-1,1),"")}

Where data A:A = the list of Clients
Where data D:D = the list of individual
Where 'summary 2'!G2 is the criteria i.e. the name of the individual

When using this formula, I am getting duplicate entries in the list, for example:

JOhn smith - Criteria

Client name
Client 1
Client 1
Client q
Client q

Is there anything I can add to the formula to stop this from happening?

Thanks so much

You shouldn't really ask your own question in someone else's thread - See Rule 01 on hijacking at the top of the screen.

You should start your own thread, and it would help if you attached a sample Excel workbook to that thread (yellow banner at the top of the screen explains how to do this). You can have a link back to this thread if you think it is relevant.

Hope this helps.

Pete

Originally Posted by Pete_UK

You shouldn't really ask your own question in someone else's thread - See Rule 01 on hijacking at the top of the screen.

You should start your own thread, and it would help if you attached a sample Excel workbook to that thread (yellow banner at the top of the screen explains how to do this). You can have a link back to this thread if you think it is relevant.

Hope this helps.

Pete

My sincerest apologies, Pete, I’ll be sure to start a new thread