Auto-Lookup Value based on formula result (VLOOKUP?)

**alexrawnsley** · 06-04-2012, 12:57 AM

Please see the attached document for an example of what I'm talking about.

I have a list of statistics for a sports team (E2:I11). I want to build a summary table, showing me the leaders in each category, and the number in that statistic (A2:C5). Now the number generation is easy, I used a MAX formula to show me the largest number in a range (each column). Now I'm stuck on the name part. I want B2, B3, B4 and B5 to automatically generate with the value in column E, based on the value in column C.

For example, in the document attached, B2 should return Guillermo Johannsen, as he has the most goals (highest number in column F). B3 should return Toney Haymaker. The table with the stats in it is sorted by points, so the B4 is easy, just a cell reference. B5 should be Rene Brien.

Is this a VLOOKUP?

**vlady** · 06-04-2012, 01:25 AM

There is no attachment. maybe repeat it again.

**micope21** · 06-04-2012, 03:07 AM

Hi

Instead use Max. Change it to Large. If column C is High score use this =IFERROR(LARGE($c$2:$c$11,ROWS($1:1)),"") Then copy down.

Then use this forumla to pick up the name=IFERROR(INDEX($B$2:$B$11,SMALL(IF($C$2:$C$11=F2,ROW($C$2:$C$11)-MIN(ROW($C$2:$C$11))+1),COUNTIF($F$2:F2,F2))),"") CTRL+SHIFT+ENTER. Then copy down.
If you got any more like points or other change =IFERROR(INDEX($B$2:$B$11,SMALL to =IFERROR(INDEX($D$2:$D$11,SMALL.Rest stay C Columns. CTRL+SHIFT+ENTER. Then copy down.

Here a Sample file