How to search for 2nd instance of identical value in a table? (VLOOKUP Issue)

[arooney88]

I have a report that looks up the top 3 errors in a table by %. Rarely are the %'s the same but sometimes they calculate out to be.

Right now I'm using a Vlookup to return my results, but if (for example) there are 2 results that = 50%, both of my return values will be for sentence1 and not for sentence 1 and 2 because the VLOOKUP stops at the first instance of 50%.

I know this is a VLookup issue, but what formula would I be able to use instead? Would this be an INDEX/MATCH instance? I tried using Index/Match in a similiar way and it's giving me the same results.

Thanks for any help everyone!


[TABLE="width: 500"]
<TBODY>[TR]
[TD]50%[/TD]
[TD]Sentence1[/TD]
[/TR]
[TR]
[TD]50%[/TD]
[TD]Sentence2[/TD]
[/TR]
[TR]
[TD]45%[/TD]
[TD]Sentence3[/TD]
[/TR]
</TBODY>[/TABLE]

 

[mikerickson]

If column A is integers, you could use A1:A100+ROW(A1:A100)/100 to get an array with no duplicates, that preserves the ordering of the values.


But, since you are using two digit percentages, perhaps

=INDEX(B:B, 1000*MOD(LARGE(100*A1:A100+ROW(A1:A100)/10000, 1),1), 1) would return the value corresponding to the highest value in column A.
=INDEX(B:B, 1000*MOD(LARGE(100*A1:A100+ROW(A1:A100)/10000, 2),1), 1) the second highest,
=INDEX(B:B, 1000*MOD(LARGE(100*A1:A100+ROW(A1:A100)/10000, 3),1), 1) the third.

 

[arooney88]

Would this problem be easier if I didn't use 2 digit percentages? Just had whole number percentages?

 

[mikerickson]

The formula is complicated because the underlying values are probably something like .23, with the cell formatted to show 23%.

The big snag would be if the percentages are calculated exactly with no rounding and the underlying values like .228676666667.

If that is the case, a UDF might be the best approach.

Modifying your underlying data, making it less precise, is a bad idea. Just let us know what you have and a solution that meets your needs can be found without disrespecting the value of accurate data.