MrExcel Publishing
Your One Stop for Excel Tips & Solutions

HLOOKUP - Help?


Posted by Len Clarke on December 08, 2000 12:14 PM

Can someone tell me what I am missing because I can't get this function to work?

Row 1 - has 5 columns, each with a company name in.
Rows 2-5 - Each company has been allocated a score based on a different criteria for each Row.

In column 6 I have shown the highest score achieved, using the MAX function, for each of Row 2-5.

In column 7, I now want the cell to compare the MAX score from the previous cell in column 6, match it to its original location and show the Company name as the result of the function.

For the first Row I keep getting the result #N/A.
And when I try to do the same function for Rows 3-5, how do I tell the function to select the result from Row 1. The function must only look at 1 Row of results each time because exactly the same score may have been achieved by a different company in another row.

Is this only possible by creating separate pivot tables for each row and testing them individually?

Thanks


Posted by Aladin Akyurek on December 08, 2000 2:17 PM

Put in G2 (column 7) the formula

=INDEX($A$1:$E$1,1,MATCH(MAX(A2:E2),A2:E2,0))

and copy this down as far as needed.

Aladin