Problem Using LARGE formula with Index/Match to pull values when there are Duplicates

**BDavis** · 09-13-2012, 07:45 AM

Good Morning,

I am attempting to use the LARGE formula to pull the top 3-5 percentages out of a field of 50-100, while using the INDEX/MATCH function to pull the corresponding "descriptor" that is associated with those top 3-5 percentages. (I need to do this across multiple data sets, but I can't get past this 'duplicate' issue) However, I am running into the problem when there are 2 percentages that are identical (WH 14 and WH 16 in pasted text below), then the INDEX/MATCH function only pulls the 1st "descriptor" and doesn't continue down to the Duplicate. I am stuck as to how to tell excel to move to the next set of duplicate data and match the 'descriptor' to that data? I appreciate any/all help!

I have attached a file that should show what I am trying to do. These are the formulas I am using right now, pulled down into the 3 cells below them to get the top 3.

B1:

Please Login or Register  to view this content.

C1:

Please Login or Register  to view this content.

Warehouse S/S %
WH 1 50.00%
WH 2 57.14%
WH 3 0.00%
WH 4 50.00%
WH 5 100.00%
WH 6 60.00%
WH 7 33.33%
WH 8 66.67%
WH9 60.00%
WH 10 63.64%
WH 11 78.57%
WH 12 55.56%
WH 13 42.86%
WH 14 71.43%
WH 15 61.54%
WH 16 71.43%

**JosephP** · 09-13-2012, 08:00 AM

I reckon one of the formula gurus here will have a better answer but this seems to work
c4: =IF(COUNTIF(B$3:B4,B4)=1,INDEX($A$50:$A$65,MATCH($B4,$B$50:$B$65,0)),INDEX($A$50:$A$65,SMALL(IF($B$50:$B$65=B4,ROW($B$50:$B$65)-ROW($B$49),""),COUNTIF(B$3:B4,B4))))
array entered with ctrl+shift+enter

see workbook attached

**BDavis** · 09-13-2012, 08:13 AM

Thank you! That works great! I am relatively new to larger formulas, and especially CSE formulas. If you have the time, could you explain briefly what you did, as I am having a challenging time putting together how the formula works?

Thanks again.

**JosephP** · 09-13-2012, 08:19 AM

=IF(COUNTIF(B$3:B4,B4)=1,INDEX($A$50:$A$65,MATCH($B4,$B$50:$B$65,0))...
the first section checks if it is the first time the percentage has appeared in the ranking list and if so it uses your original formula

INDEX($A$50:$A$65,SMALL(IF($B$50:$B$65=B4,ROW($B$50:$B$65)-ROW($B$49),""),COUNTIF(B$3:B4,B4))))

the bold section returns all the row numbers that match the percentage in question and subtracts the row above the table to convert the row numbers into 1, 2, 3 etc to use in the INDEX formula.
the COUNTIF part is used by the SMALL function so if it's the second time the percentage has appeared in the ranking the formula uses the 2nd lowest row number and so on

does that help explain it?

**MarvinP** · 09-13-2012, 08:21 AM

Hi BDavis and welcome to the forum (I guess this is twice for you...)

A pivot table answer will work for you. In Pivots there is a Top.. Value Filter to do what you want. See the attached for the Pivot Table answer. No formulas needed.

**BDavis** · 09-13-2012, 08:27 AM

Yes, Thank you, that helps a lot. If I want to use this formula on another set of data that will be right next to the original set, I can just modify the 'absolute' cells you have marked, correct? Will I have to modify anything further?

Thanks again for your help!

**JosephP** · 09-13-2012, 08:29 AM

yes that oughta work. post back if you get stuck and we'll fix it :-)

**BDavis** · 09-13-2012, 08:29 AM

Thanks MarvinP! I will definitely use this option as well on the other sets of data I have to sort this way! I really appreciate the help!

**kitthada** · 08-11-2014, 01:47 PM

Hi,
I have downloaded the attached file posted by JosephP. But after clicking & enter on cell "C6", I get an error "#VALUE!", looking the formula, there is "{...........}", i don't know if it's related or not.
I'm trying to redo the attached excel file, converting the format, currently the data is presented in vertical, while i need it in horizontal form.
Can someone remake the attached excel file with two sheets, first one vertically and the second horizontally

Please help...
Thanks

**protonLeah** · 08-11-2014, 02:23 PM

@kitthada,
Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Problem Using LARGE formula with Index/Match to pull values when there are Duplicates

LinkBack

Thread Tools

Rate This Thread

Display

Problem Using LARGE formula with Index/Match to pull values when there are Duplicates

Re: Problem Using LARGE formula with Index/Match to pull values when there are Duplicates

Re: Problem Using LARGE formula with Index/Match to pull values when there are Duplicates

Re: Problem Using LARGE formula with Index/Match to pull values when there are Duplicates

Re: Problem Using LARGE formula with Index/Match to pull values when there are Duplicates

Re: Problem Using LARGE formula with Index/Match to pull values when there are Duplicates

Re: Problem Using LARGE formula with Index/Match to pull values when there are Duplicates

Re: Problem Using LARGE formula with Index/Match to pull values when there are Duplicates

Re: Problem Using LARGE formula with Index/Match to pull values when there are Duplicates

Re: Problem Using LARGE formula with Index/Match to pull values when there are Duplicates

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions