Search code examples
google-sheetsmax

Find the value of cell on same row as maximum in another column

Basically i have absorbance per wavelength, wavelength in col A and absorbance in col B, i want to find out at which wavelength the absobance is highest, how would i do that?

ex:

A: B:
500 1000000
501 1200000
502 1400000
503 1300000
504 1000000

I want the next column to give the answer 502

Note: I dont know what is up with that table but i think you get the idea...

Solution

  • Use XLOOKUP() with MAX() function.

    =XLOOKUP(MAX(B:B),B:B,A:A)

    For older version of excel you can use INDEX/MATCH combination.

    =INDEX(A:A,MATCH(MAX(B:B),B:B,0))

    enter image description here

    Another option is QUERY()

    =QUERY(A:B,"select A order by B DESC limit 1")

    enter image description here