quick Excel lookup query

[suki]

Hi

I am just trying to do a 'negative Hlookup' with some data i have.

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
Damian

 

[Tomsk]

step 1 : in cell G4 insert the following formula =IF(F4>G3,F4,G3)
step 2 : copy this formula (allowing excel to make the relative adjustments) to fill G5 through to G9
step 3 : your required result will come from : =INDEX(B3:E3,1,MATCH(INDEX(F4:F9,MATCH(G9,G4:G9,0),1),INDIRECT("B"&MATCH(G9,G4:G9,0)+3&":E"&MATCH(G9,G4:G9,0)+3),TRUE))

If your data block is bigger, then you will have to change the formula to reflect the different cells and ranges.

This doesn't take into account duplicate values in column F

 

[whitecrook]

	1	2	3	4				
	math	eng	geo	art		MAX	NAME	ID
a	42	45	45	22		=MAX(B13:E13)	=HLOOKUP(I13,$B$11:$E$18,2)	=MATCH(G13,B13:E13,0)
b	1	2	3	4		=MAX(B14:E14)	=HLOOKUP(I14,$B$11:$E$18,2)	=MATCH(G14,B14:E14,0)
c	44	5	55	66		=MAX(B15:E15)	=HLOOKUP(I15,$B$11:$E$18,2)	=MATCH(G15,B15:E15,0)
d	66	22	21	1		=MAX(B16:E16)	=HLOOKUP(I16,$B$11:$E$18,2)	=MATCH(G16,B16:E16,0)
e	44	7	55	3		=MAX(B17:E17)	=HLOOKUP(I17,$B$11:$E$18,2)	=MATCH(G17,B17:E17,0)
f	22	8	88	4		=MAX(B18:E18)	=HLOOKUP(I18,$B$11:$E$18,2)	=MATCH(G18,B18:E18,0)

paste that in and see if it helps. It doesn't work if you have dup max values across subjects.