Hi Nick,
To avoid getting the same lookup result when duplicates are present, you can use a combination of the
INDEX,
MATCH, and
COUNTIF functions. Here's how you can do it:
[list=1][*] In a separate column, use the
COUNTIF function to count the number of times each value appears in the original data. For example, if your data is in columns A and B, you can use the formula
Formula:
=COUNTIF($B$2:B2,B2)
in cell C2 and drag it down to the end of your data.[*] In another column, use the
INDEX and
MATCH functions to look up the value you want based on the count of the value. For example, if you want to look up the second occurrence of the value 12, you can use the formula
Formula:
=INDEX($A$2:$A$7,MATCH(2,C$2:C$7,0))
.
Here's how this works:
- The
MATCH function looks for the value 2 in the count column (C) and returns the row number where it's found.
- The
INDEX function uses that row number to return the corresponding value from the original data column (A).
By using the
COUNTIF function to count the occurrences of each value, you can ensure that you get a different lookup result for each occurrence of a value.