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))
Another option is QUERY()
=QUERY(A:B,"select A order by B DESC limit 1")
