If with vlookup by max number

[kypok999]

Dear all,

A bit stuck here

I am trying to find a formula based on max number

Column A I have lets say A2=Chemical WasteLU2 7BZ, B2 = Luton C2=1

NExt line is same but different in column b and c as per below:

A2=Chemical WasteLU2 7BZ, B3 = Warrington C3=2

So I need to always show result based on highest number in Column C in aboive case it would be Warrington based on unique identifier in column A3 and C3 is more then C2

Thank you

 

[Scott T]

Try

Book1
	A	B	C
1	Chemical WasteLU2 7BZ	Luton	1
2	Chemical WasteLU2 7BZ	Warrington	2
3
4	Warrington
Sheet1

Cell Formulas
Range	Formula
A4	=INDEX(B1:B2,MATCH(MAX(C1:C2),C1:C2,0))

Note if there is a tie in the largest number then index will return the first match it finds.
Also Vlookup looks to the right. If you want to look to the left with vlookup you would have to create the correctly ordered columns with choose.

 

[kypok999]

Thank you Scott I forgot to mention it should look for unique identifier which can be different in column a

Try

	A	B	C
1	Chemical WasteLU2 7BZ	Luton	1
2	Chemical WasteLU2 7BZ	Warrington	2
3
4	Warrington

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula A4 =INDEX(B1:B2,MATCH(MAX(C1:C2),C1:C2,0)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Note if there is a tie in the largest number then index will return the first match it finds.
Also Vlookup looks to the right. If you want to look to the left with vlookup you would have to create the correctly ordered columns with choose.

 

[mail2master]

Same formula... combine the condition with AND() function.

 

[Aladin Akyurek]

Control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($B$2:$B$6,SMALL(IF($A$2:$A$6="a",IF($C$2:$C$6=MAX(IF($A$2:$A$6="a",$C$2:$C$6)),ROW($B$2:$B$6)-ROW($B$2)+1)),ROWS($1:1))),"")

Replace "a" with a relevant id which the range in column A must meet.