VLOOKUP search from bottom to top ?

**WinteE** · 06-04-2007, 05:01 AM

What do you mean with "current row" ?

**Jazzer** · 06-04-2007, 05:29 AM

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

**KASTEO** · 06-04-2007, 07:05 AM

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.

**daddylonglegs** · 06-04-2007, 07:50 AM

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)

**Jazzer** · 06-04-2007, 08:06 AM

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

**KASTEO** · 06-04-2007, 09:15 AM

Ahh... I got it now. Both are work wonderfully.

Thank you,
Kongkeat K.

**nugundam93** · 04-02-2008, 03:54 PM

i hope it's ok with the OP to post here a question about getting vlookup to start from the bottom up, since it's the same topic and i don't want to create a new thread when there's an existing one already.

i've been trying to get daddylonglegs' post to work on an excel sheet i'm working on but i can't seem to make it work. here are the details on the sheet:

-let's say current row is 2736
-agent's name is in column G, agent's current stage is in column O, and agent's past stage is in column Y
-agent's current stage is just +1 to past stage for every week it: example - mike's stage last week ending march 21 was stage 1 so if mike appeared again in week ending march 28, his previous stage is now listed as stage 1 and current stage is automatically listed as stage 2. rinse, repeat as needed.

so let's say i'd like to get Y2736 to show agent's past stage based on the last time agent's name appeared, looking from row 2735 up to row 2 (row 1 has the headings)

thanks!

**NitoA** · 05-25-2012, 04:40 PM

Originally Posted by Jazzer

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

I used the formula, it works great, but i can't understand how it works! Can you pleas explain it?
Thanks for sharing!