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

Level	Part
1	Truck
2	Engine
3	Crank Shaft
3	Cam Shaft
3	Cylinder
4	Piston
4	Piston Rod
2	Transmission
3	Gears
3	Case
3	Shaft

<tbody>
</tbody><colgroup><col><col></colgroup>

 

[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