Hi
I am just trying to do a 'negative Hlookup' with some data i have.
\1
Using the data above, column F has the largest values from each row.
What i want to do is, use this largest value, look it up in the row and return the column name.(eg. maths or english.) how can i avoid the problem of having duplicate numbers also ?
is this possible ?
thanks
In cell G3 put this formula, then fill it down.
=INDEX($B$3:$E$3,MATCH(F4,$B4:$E4,0))
Trish in Oz
-------------
A problem well defined is a puzzle half solved
If you attach a sample of your workbook it will be easier to find a solution.Remember to remove/replace sensitive data before uploading the file. Look here if you need help with attachments:
http://www.excelforum.com/faq.php?fa...b3_attachments
What if row 7 has 66 in math and English?
If account for duplicates then try this formula below.
Input formula in cell G4 and copy across and copy down.
Or if don't like to see the formula errors then input formula in cell G4 and copy across and copy down
=INDEX($B$3:$E$3,SMALL(IF($B4:$E4=$F4,COLUMN($B4:$E4)-COLUMN($B4)+1),COLUMN(A4)))
Both formulas are arrays. Paste the formula in cell G4 double click inside the cell and hold down.=IF(COUNTIF($B4:$E4,$F4)>=COLUMNS($G$4:G4),INDEX($B$3:$E$3,SMALL(IF($B4:$E4=$F4,COLUMN($B4:$E4)-COLUMN($B4)+1),COLUMN(A4))),"")
Ctrl,Shift,Enter
Hope it helps!
Last edited by vane0326; 09-16-2007 at 07:07 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks