VLOOKUP from bottom to top

1. VLOOKUP from bottom to top

Hi Folks,

I would like to perform a VLOOKUP operation & return values based on a match from bottom to top. By default, it would return the values corresponding to the top most match in the table array. I wanna do it the other way round.

I know there should be a way to do this. May be by using the LOOKUP function. But, nothing is striking me right now.

Regards,
Karan

2. Re: VLOOKUP from bottom to top

Hi, you could try something like

=LOOKUP(2,1/(A2:A100=C2),B2:B100)

In the example C2 is what you are searching in A2:A100 for the correspondence in B2:B100.

The number 2 can be substituted by any number greater than 1.

Hoping it's a little step forward for your problem.

3. Re: VLOOKUP from bottom to top

Well. That does solve the problem. Thanks a lot.

Can I know what is the significance of the number 2 or any number greater than 1 in place of the first argument?

Also, the significance of dividing 1 by the boolean array in the second argument. The Excel help doesn't mention anything about all this.

Thanks again.

Regards,
Karan

4. Re: VLOOKUP from bottom to top

Hi, I've found a clear explanation

My elementary English would sound very strange (maybe funny).

Regards,

5. Re: VLOOKUP from bottom to top

The link provided is excellent (unsurprising given the author - MS Excel MVP)

FWIW - a reaffirmation if nothing more: http://www.excelforum.com/2252293-post5.html

6. Re: VLOOKUP from bottom to top

What an explanation!!!

That was a great piece of learning.

Thanks,
Karan

7. Re: VLOOKUP from bottom to top

How can this be done using 2 variables? I want the formula find the last occurence of 2 criteria and return a value from a 3rd column in that same row. Can you nest a AND statement in it? Using the formula above, =LOOKUP(2,1/(A2:A100=C2),B2:B100), I would want not only to search for C2 in Column A but also value D2 in Column E and then return value from Column B like it was originally looking for.

8. Re: VLOOKUP from bottom to top

zoefrannie,

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1