vlookup with max

[turtel]

Hi all,


I am trying to find a max value for a Results column (D14:D18) and getting the corresponding abnormality in the abnormality column (A14:A18). The abnormality column is in text. I tried =VLOOKUP(MAX(D14:D18),D14:D18,A14:A18,0)but was getting a #REF! as a result. Any help would be truly appreciated, thanks in advance.

RG

 

[Luke M]

VLOOKUP needs the entire range (including reference) as 2nd argument, and col number as 3rd argument. However, you''ll also find that it can't "go left". We can resolve via INDEX and MATCH:

=INDEX(A14:A18,MATCH(MAX(D14:D18),D14:D18,0))

 

[turtel]

Oh OK, the formula worked, so thank you very much.