reverse vlookup how - duplicate issue

Hi

I have just used this formula from a previous thread in here (reverse vlookup how)

=INDEX($A$1:$A$7,MATCH(F10,$D$1:$D$7,0))

and it works great. I am using the formula =large('MR Data'!M:M,1) to get my top 10 oldest items from a list of activities and then this formula to populate other fields against it. The problem I have come across is that in the top 10 there are some duplicates so when i do the lookup above they pull the corresponding data to the first one in the list and not each item. How can I do this?

Thankyou in advance, Kerry

Try this formula in C3:

=INDEX(Sheet1!A$2:A$100,SMALL(IF(Sheet1!$K$2:$K$100=$I3,ROW(Sheet1!$K$2:$K$100)-ROW(Sheet1!$K$2)+1),COUNTIF($I$3:$I3,$I3)))

confirmed with CTRL+SHIFT+ENTER not just ENTER and copied down.

If any of the columns have unique values only, like the Employee Name perhaps.. then for better efficiency, apply the formula above only to column D in your sheet, then use your original Index/Match configuration to get the other columns....

Array formulas can be inefficient if a lot are used and if the ranges are large....

also the Date column (H) may need to be formatted as Date.

I've put new formulae and changed some of your others in the attachment, which now avoids duplicates and reports correctly.

Hope this helps.

Pete

Here is my version....

Yes, it should. The formula is designed to take next unique match for the duplicated rank, so that the same data should not be duplicated unless the data itself is duplicated in the original database.