VLOOKUP search from bottom to top ?

Is there any way to force VLOOKUP to search data from the current row up to the top row?
Normally VLOOKUP will search data from top to bottom, right? But when I have a long list of data and I want to get the latest data, I can't use VLOOKUP because it always fetch the first one, not the latest one. Is there any way to fix this problem?

Thanks,

Hi,

here is an example of kind of "reverse vlookup". It returns data from the last row that matches the criteria.

In D2, you can change the value you are looking for from the A column and in E2 it returns a number from volumn B. This is done with normal VLOOKUP.

In G2:H2 is the same thing, but with an array formula that returns the last match:

{=INDEX(B2:B22,MAX(ROW(A2:A22)*(G2=A2:A22))-1)}

Is this what you are looking for? If you need more info about the formula, just ask.

- Asser

Thank Jazzer, yes that is what I'm looking for, but I'm not quite understand how it work. As you can see in the file I attached. I edited something in your file and then it gave me an error. I'm not sure what I'm doing wrong.

Jazzer's suggested formula is an "array formula", as he says.

You need to confirm the formula with CTRL+SHIFT+ENTER so that the curly braces appear automatically around the formula in the formula bar. To do this select H2 press F2 and then hold down CTRL and SHIFT keys while pressing enter.

You could use an alternative formula which is just "normally entered" - in H2 copied down

=LOOKUP(2,1/(A$2:A$22=G2),B$2:B$22)

Originally Posted by daddylonglegs

=LOOKUP(2,1/(A$2:A$22=G2),B$2:B$22)

That is a brilliant formula! Thank's daddylonglegs.

And for KASTEO: Yes, it's an array formula (well not that one). Array formulas are one quite powerfull feature in Excel. You can learn more about them for example from here or here.
- Asser