Get value from a range based on maximum

[krllb]

Hi Gurus,

Newbie questions. How can I get value "Yellow" from a range based on this criteria. If bird is entered then it will get the maximum value in column A which is yelllow.
Many thanks in advance.

Regards,
Krll

 

[SunnyAlv]

try

=INDEX(C2:C10,MATCH(1,(B2:B10=E2)*(A2:A10=MAX(A2:A10)),0))

 

[awoohaw]

This may be clunky, but works. You need to adjust the ROW() part of the formula depending on where your first row of data is.
This should work regardless of the sort order.
It requires that you have a version of excel with the MAXIFS function.(please update your profile so the version shows on your button).

Book1
	A	B	C	D	E	F
1					ENTRY:	Color of Max
2	2	dog	red		bird	green
3	1	dog	blue
4	2	Bird	green
5	1	Bird	yellow
Sheet6

Cell Formulas
Range	Formula
F2	F2	=INDEX($C$2:$C$5,MAX((ROW($A$2:$A$5)-1)*((($B$2:$B$5=E2)*($A$2:$A$5))=(MAXIFS($A$2:$A$5,$B$2:$B$5,E2)))))

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

If you have 365 another option is

=TAKE(SORT(FILTER(A2:C100,B2:B100=E2),1,-1),1,-1)

 

[awoohaw]

MAXIFS is also only in 365 and 2019 and later versions, it seems.
If you don't have these then try this, it is similar to what @SunnyAlv suggested, but allows for the data to not be sorted:

=INDEX($C$2:$C$5,MATCH(TRUE,MAX((($B$2:$B$5=E2)*($A$2:$A$5))*($B$2:$B$5=E2))=(($B$2:$B$5=E2)*($A$2:$A$5))*($B$2:$B$5=E2),0))

 

[krllb]

Thank you everyone. I was able to get it to work using solution from @awoohaw.

Good bye paracetamol