How to perform reverse search (Bottom to top)

[mk5584]

Hi,

Is it possible to search in a array from bottom cell to top cell (Vlookup does from top to bottom), In the below table I need a result of "Shaft" for input 3, whereas vlookup will give "Crank shaft" as a result

[TABLE="width: 152"]
<tbody>[TR]
[TD]Level[/TD]
[TD]Part[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Truck[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Engine[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Crank Shaft[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Cam Shaft[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Cylinder[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Piston[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Piston Rod[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Transmission[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Gears[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Case[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Shaft[/TD]
[/TR]
</tbody><colgroup><col><col></colgroup>[/TABLE]

 

[azumi]

Assumed your data in ranges A1:B12, based on your table, and input criteria in Cell D2 (ie: 3)
Put this in Cell in E2 and copied down:

=INDEX(B:B,MAX((A2:A12=D2)*ROW(A2:A12)))

Array Formula --> hit CTRL-SHIFT-ENTER button together


Azumi

 

[azumi]

or see the example:
https://www.dropbox.com/s/bgf7atrjyy6ec7q/Maximum Lookup.xlsx

 

[mk5584]

Hello Azumi,

Thanks, I worked wonderfully


Karthik