#N/A error with VLOOKUP function
 

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);A1:D4 4;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.

#N/A error with VLOOKUP function
 

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" wrote:

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);A1:D4 4;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.

#N/A error with VLOOKUP function
 

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

=INDEX(B:B;MATCH(MAX(D5;D11;D17;D22;D28;D33;D38;D4 4);D: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.

"Mike H" wrote:

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" wrote:

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);A1:D4 4;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.

#N/A error with VLOOKUP function
 

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"Amer" wrote in message
...
Here is the new formula and the result is '0' !!!

=INDEX(B:B;MATCH(MAX(D5;D11;D17;D22;D28;D33;D38;D4 4);D: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.

"Mike H" wrote:

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" wrote:

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);A1:D4 4;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.