#N/A error with VLOOKUP function

[Amer]

I am trying to get the highest value in one column and return the
corresponding value of the same row using the following function:

=VLOOKUP(MAX(D5;D11;D17;D22;D28;D33;D38;D44);A144;2;FALSE)

But I am receiving this error: #N/A

After checking the error, there is something wrong with the 'col_index_num'
part of the formula. This number should refer to column 'B' in my sheet.

 

[Mike H]

Hi,

I'm struggling to understand you formula so perhaps this will help

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

Finds the maximum value in column A and returns corresponding value in
Column B. It looks like you use ; and not , so make necessary changes.

Mike

Mike

 

[Amer]

Here is the new formula and the result is '0' !!!

=INDEX(B:B;MATCH(MAX(D5;D11;D17;D22;D28;D33;D38;D44);D;0))

Note that I am using Excel 2007. It changes the ',' to ';'.

(D5;D11;D17;D22;D28;D33;D38;D44) are selected cells in column 'D'. No need
for Excel to check the whole column.

Thanks for the help.

 

[Niek Otten]

=INDEX(B5:B44;MATCH(MAX(D5;D11;D17;D22;D28;D33;D38;D44);D544;0))