VLOOKUP HELP! How can I return a value the 2nd time it's in my table??

[arooney88]

[TABLE="width: 250"]
<TBODY>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]15%[/TD]
[TD]Sentence1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]10%[/TD]
[TD]Sentence2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]50%[/TD]
[TD]Sentence3[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]30%[/TD]
[TD]Sentence4[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]50%[/TD]
[TD]Sentence5[/TD]
[/TR]
</TBODY>[/TABLE]

[TABLE="width: 250"]
<TBODY>[TR]
[TD]Top Errors[/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]50%[/TD]
[TD]Sentence3[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]50%
[/TD]
[TD]Sentence3[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]30%[/TD]
[TD]Sentence4[/TD]
[/TR]
</TBODY>[/TABLE]


Hey everyone and thanks for looking into my problem.

I have a table above (1st on top) that I'm having an issue with. I need to return the top 3 error %'s sentences for a monthly trending error report. My issue is that the formula I'm using only works if the top 3 %'s aren't matching at all.

Right now I'm using these formulas in columns A and B for the 2nd table.

Column A Column B
=Large(A2:B5, 1) =iferror(Vlookup(A2, A2:B5, 2, False)
=Large(A2:B5, 2) =iferror(Vlookup(A3, A2:B5, 2, False)
=Large(A2:B5, 3) =iferror(Vlookup(A4, A2:B5, 2, False)


How can I return the correct 2nd occurance of 50% error that should return Sentence 5 from the first table??

Any help would be fantastic and thanks for looking into my issue!!













asdfasdf

 

[Redwolfx]

[TABLE="width: 250"]
<TBODY>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]15%
[/TD]
[TD]Sentence1
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]10%
[/TD]
[TD]Sentence2
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]50%
[/TD]
[TD]Sentence3
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]30%
[/TD]
[TD]Sentence4
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]50%
[/TD]
[TD]Sentence5
[/TD]
[/TR]
</TBODY>[/TABLE]

[TABLE="width: 250"]
<TBODY>[TR]
[TD]Top Errors
[/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]50%
[/TD]
[TD]Sentence3
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]50%
[/TD]
[TD]Sentence3
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]30%
[/TD]
[TD]Sentence4
[/TD]
[/TR]
</TBODY>[/TABLE]


Hey everyone and thanks for looking into my problem.

I have a table above (1st on top) that I'm having an issue with. I need to return the top 3 error %'s sentences for a monthly trending error report. My issue is that the formula I'm using only works if the top 3 %'s aren't matching at all.

Right now I'm using these formulas in columns A and B for the 2nd table.

Column A Column B
=Large(A2:B5, 1) =iferror(Vlookup(A2, A2:B5, 2, False)
=Large(A2:B5, 2) =iferror(Vlookup(A3, A2:B5, 2, False)
=Large(A2:B5, 3) =iferror(Vlookup(A4, A2:B5, 2, False)


How can I return the correct 2nd occurance of 50% error that should return Sentence 5 from the first table??

Any help would be fantastic and thanks for looking into my issue!!


asdfasdf

Table 1

Cells A1:B5

Summary Table Cells E1:F3

In place of your Vlookups

E1=Large(A2:B5, 1)
E2=Large(A2:B5, 2)
E3=Large(A2:B5, 3)


F1 =INDEX($B$1:$B$9,SMALL(IF($A$1:$A$9=$E1,ROW($A$1:$A$9)),COUNTIF($E$1:E1,E1)))

Confirm with Ctrl+Shift+Enter, then drag down

 

[arooney88]

Thanks!

This works when I tried another example. Now I just need to understand that logic a bit better. Thanks again for your help!

 

[arooney88]

What does the Ctrl+Shift+Enter do exactly when you hit those keys inside a function? I see that it addes { } but not sure what that does?

 

[Redwolfx]

By Confirming with Ctrl+Shift+Enter you are making Excel create an Array Function. This was/is a very powerful tool once you begin to use it properly. There are a number of resources online about creating arrays.