Index/Match with multiple results

Perhaps you want to filter the data so only the appropriate row shows? Advanced Filters?

We need a sample workbook to see exactly what you mean when you say "Display Both".

Assuming your data is in A2:B100 and A2:A100 contains the Names and B2:B100 the associated values.

Now Assume you have listed the top 20 in C2:C21, then D2 enter formula:

Please Login or Register  to view this content.

The formula must be confirmed with CTRL+SHIFT+ENTER and not just ENTER, then copied down.

The formula is a sort of Vlookup for multiple matches... It uses the Index() function which requires you to index the table or column to extract from and it requires the Row number to extract from which is gotten from this part: SMALL(IF($B$2:$B$100=C2,ROW($B$2:$B$100)-ROW($B$2)+1),COUNTIF($C$2:$C2,$C2))

The Small() function just allows us to step up and extract one match at a time, starting from the first match found. It will extract only if a the number in column C is found in range B2:B100 and then it will return the corresponding row number within the range. The -ROW($B$2)+1 is added for robustness (incase you insert rows above, then the result won't skew) and to offset the number of rows from row the top of the sheet you are starting the indexing.

The last COUNTIF($C$2:$C2,$C2) is the k factor for the Small() function, which is like a step factor in a For...next loop in VBA.

The CSE confirmation is because it is an array formula and you have to confirm with those keys to make these formulas work.

Then you copy down... The only thing that changes copying down is the COUNTIF($C$2:$C2,$C2) . again to determine the step or row number we're in, and it figures this out by counting how many times the same number has appeared from the top of the range to the current location.

Hope this helps.

Better if you started a new thread.