Forum Discussion
Return a value from column adjacent to query
I have a very simple table with a name column and a number column. https://www.baseball-reference.com/players/c/crawfsa01.shtml 16 https://www.baseball-reference.com/players/l/lajoina01.shtml...
=INDEX('1901'!B2:B372, MATCH(MAX('1901'!C2:C372), '1901'!C2:C372, 0))
or
=XLOOKUP(MAX('1901'!C2:C372), '1901'!C2:C372, '1901'!B2:B372)
Instead of MAX('1901'!C2:C372), you can use the address of the cell with the formula =MAX('1901'!C2:C372)
Thanks!
So since I'm filling in two separate cells with the number and corresponding name, would it be inverted from each other?
To return the name:
=INDEX('1901'!B:B,MATCH(MAX('1901'!C:C),'1901'!B:B,0))
To return the number:
=INDEX('1901'!C:C,MATCH(MAX('1901'!C:C),'1901'!C:C,0))
So since I'm filling in two separate cells with the number and corresponding name, would it be inverted from each other?
To return the name:
=INDEX('1901'!B:B,MATCH(MAX('1901'!C:C),'1901'!B:B,0))
To return the number:
=INDEX('1901'!C:C,MATCH(MAX('1901'!C:C),'1901'!C:C,0))
The formula for the number would be simply
=MAX('1901'!C:C)
Let's say that you enter this formula in A2. The corresponding name is returned by
=INDEX('1901'!B:B,MATCH(A2,'1901'!C:C,0))
or
=XLOOKUP(A2,'1901'!C:C,'1901'!B:B)