Forum Discussion
Return a value from column adjacent to query
I have a very simple table with a name column and a number column.
| Sam Crawford* | 16 |
| Nap Lajoie | 14 |
| Buck Freeman* | 12 |
| Jimmy Sheckard* | 11 |
| Jesse Burkett* | 10 |
In a separate sheet I have a query to return the maximum number from the number column.
=MAX('1901'!C2:C372)
I'd like to return the name next to that number as the next query.
Some more recent options:
= TAKE(SORTBY(Name, Number, -1), 1)gives just the name, whilst
= TAKE(SORT(simpleTable, 2, -1), 1)gives both the maximum value and the associated name.
=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))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)
