find cell with maximum value that meets a certain criteria

[shrijan]

hi all excel gurus,

i am looking for a formula to find a cell that has the highest value after meeting a certain criteria. in the attached, i need to find the cell in column B that has the highest value in column F after meeting criteria in column E.

As an example, in rows 3-4, there are two values in column F under the same criteria in column E3-E4. so i want my formula to return 35FINQ40 as a the output (form column B) base on the highest value in column F (3,067.57) for segment FIN8G in column E.

Please help

thank you

S

[NeedForExcel]

Hi,

I guess you have forgotten to attach the file..

Please attach it..

[shrijan]

thank you. just attached.

[NeedForExcel]

Maybe something like this?

=INDEX($B$3:$B$10,MATCH(MAX(IF(I3=$E$3:$E$10,$F$3:$F$10,"")),$F$3:$F$10,0))

See the file attached.

[shrijan]

sorry. did not work. i throws out values.

[NeedForExcel]

I'v attached the file.. Did you see that?

[shrijan]

i did but when i used the formula to a larger data set, it is throwing errors.

[NeedForExcel]

It seems you are messing up with Cell References..

Have you checked that?

[shrijan]

i have attached the issue.

[NeedForExcel]

The formula is right.. However, I forgot to tell you one thing..

You have to confirm the formula by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

My Fault..

[shrijan]

worked. thank you.

[XOR LX]

=INDEX($B$3:$B$10,MATCH(MAX(IF(I3=$E$3:$E$10,$F$3:$F$10,"")),$F$3:$F$10,0))

This formula is logically flawed, and may not always give correct results.

For example, using the file you attached in post #4, change the value in cell F3 to 1332.82929 (so that it is identical to that in cell F6). The formula in cell J5 now returns 35FINQ40, though this BU is not even associated with FIN9G.

You need to repeat the conditional statement within MATCH's lookup_array also, i.e.:

=INDEX($B$3:$B$10,MATCH(MAX(IF(I5=$E$3:$E$10,$F$3:$F$10)),IF(I5=$E$3:$E$10,$F$3:$F$10),0))

which now gives the correct result of 35FINA58.

Regards

[NeedForExcel]

You are right XOR LX.

Thank you for the input.

[XOR LX]

You are right XOR LX.

Thank you for the input.

You're welcome!

[shimaa01234]

Genius formula
God bless you Mister "XOR LX"

[XOR LX]

Genius formula
God bless you Mister "XOR LX"

You're welcome!