MATCH and INDEX function retunr the same value

[albertoro]

Hi Everyone I am building a table with two colomns displaying name and amount.

I have created a formula to dislpay the top 5 sell(D49 to D53) and another formula (A49 to A53) to display the name of that particular sales. and this works.

the problem is if i have two amount which are the same. It will display the same name and not the different name. for example A39 and A40 they both sold the same amount but they have different name.

therefore in the "Top 5 sales" table A51 and A52 should have different value.

i am attaching a sample file hoping that someone could help.

thank you.

[tigeravatar]

albertoro,

Welcome to the forum!
Using your posted workbook, use this formula in cell A49 and copy down:

Please Login or Register  to view this content.

[albertoro]

Tigervatar thank you for your response. I have applied the formula you gave me and works for some cell and for some other give me the result as #N/A

=INDEX($A$2:$A$40,MATCH(1,INDEX(($D$2:$D$40=D49)*(COUNTIF(A$48:A48,$A$2:$A$40)=0),),0))

Using your formula if you have two fields with the same name is fine but you have the same ammount in two field that have different name i get #N/A in one

I think the error is in this part of the formula (COUNTIF(A$48:A48,$A$2:$A$40)=0),),0))

much appreciated.

[tigeravatar]

#N/A is the error that results when Match can't find a match. It just means that there weren't enough items to return, so the last results were errors. If you'd rather they be blank cells, nest the formula in an IFERROR:

Please Login or Register  to view this content.

[albertoro]

Hi tigeravatar, i rather not have the blank field but the result there.

I am Attaching a new file. As you can see row A38 and A39 they have same name but different mount.

In row A48 and A49 only one name is reflected the other one doent not find it.

is the formula wrong or is just not possible to have 2 names which are the same?

thank you for the help.

[Tony Valko]

I am Attaching a new file. As you can see row A38 and A39 they have same name but different mount.

I think you may have attached the wrong file.

[albertoro]

yes you are right.

here you go. thank you.

[Tony Valko]

OK, so you want to create a top 5 list?

A top N list may have more than N items depending on ties in the numeric range.

[albertoro]

I want to create a top 5 or 10 list where it also pick up the corresponding name associated to that amount.

tha problem is that if i have 2 names whic are the same it only pick up one.

[Tony Valko]

Here's an example of a top 5 list. In this case there are actually 7 items in the list due to ties.

I have the sales values sorted in descending order just to make it easier to see what the results should be.

The formula in column G is an array formula.

Array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

[albertoro]

thank you i will take a look and come back to you.

have a good evening or day....

[albertoro]

Hi Tony, thank you for the file you sent me which worked just fine for my purpose.

[Tony Valko]

Good deal. Thanks for the feedback!