Excel to return lookup value besides the first matching value

[MiniP]

First of all, I apologize if I am posting in the wrong section and please help me move it to the right place if possible.

Our team has to review customer inquiries to see what is the trend.
with the data we collected, I would like to set up a table that easily show what is the Top 5 reasons customer contacted us for.

I used this formula to pull up the top 5 based on the percentage:
eg. in cell C2
=LARGE('[Shift Captain.xlsm]Calls'!$E:$E,{1})

Then used the XLOOKUP to align the data
eg. in cell A2
=XLOOKUP($C2,'[Shift Captain.xlsm]Calls'!$E:$E,'[Shift Captain.xlsm]Calls'!$C:$C)

It works out perfectly until when we have multiple call reasons with the same value (% or number of calls)

eg. We have 1 customer called due to "Can No Longer Afford " but we also have 1 customer called because they need to "Update Account Information" ...

so both reasons are listed for the Top 4th and Top 5th reasons
However, the XLOwill only return "Can No Longer Afford ", but never return other reasons with the same amount of customers / percentage.



I there anyway we could build this table to pull all the Top 5 call reasons even if some of the reasons have an equal amount of customer calls?

I have attached the Excel file here

and also the screenshots in case the excel attached doesn't work for youdataTable.JPG

Thank you in advance.

[TMS]

A slight modification to the formula calculating the percentage. It adjusts the value by a very small amount based on the row number. At 2 decimal places, this is imperceptible in the display, however, it is enough to separate duplicates.

E4:

Please Login or Register  to view this content.

copied down.

These Dynamic Array Formulae return the values you require.
J7:

Please Login or Register  to view this content.

K7:

Please Login or Register  to view this content.

L7:

Please Login or Register  to view this content.

[MiniP]

Thank you TMS for your respond.

I learnt about UNIQUE but never thought about using it in this worksheet so I was impressed seeing you using this.

I tried the formulas you provided except the % one, it didn't work
However, I see it working with the % formula you recommended me to update

I am just wondering since each of the two reasons had the same amount of customers (1) called in .... why would the percentage be different with additional decimal added?
Is it a bug with Excel that turns out to be a good thing in my situation?


Sorry if this is a stupid question!

[TMS]

This:

Please Login or Register  to view this content.

is what makes the whole thing work. The Dynamic Array Formulae are just nice to have but only work in Excel 365.

This: -ROW()/100000000000 is enough to make a difference although it won't be visible.

That formula to calculate/adjust the percentage makes all the duplicates unique. As it turns out, you have LOTS of duplicates (at 4%) … this just picks the first two it comes across.

In what way does it not work? You need to put it in cell E4 and then copy it down, ignoring blanks.

[TMS]

Is it a bug with Excel that turns out to be a good thing in my situation?

No, it's just a mechanism to make each calculation unique … you only have one of each row number.

[TMS]

In actual fact, it doesn't need to use UNIQUE. Initially, I put that in to remove the duplicate percentages but realised I don't want/need to do that … I just needed to make the percentages unique.

L7 can be just:

Please Login or Register  to view this content.

[MiniP]

Thank you for all your prompt respond
it all makes sense to me now and I just feel how amazing and creative you are with all this way to make things work.

[TMS]

You're welcome. Thanks for the rep.



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.