Reverse search using lookup / vlookup

Desmond

New Member
Joined
Nov 8, 2005
Messages
10
My problem is that I wish to do a lookup/vlookup on a table , where the first column is in reverse order.
Column A has dates sorted in reverse order, the most recent at the top, and frequently the same date is repeated many times.
Using lookup or vlookup, I find that when the same date is repeated the one farthest down column A is used to provide the result, when in fact I need the higher one to be used for the result. I accept that some other worksheet function/s might do the trick, and would prefer not to use a macro.
Can someone please assist?
TIA.
Des.
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
Hello,


Try adding a ,0 at then end of you vlookup, this gives the first match

i.e. =VLOOKUP(A1,C1:D20,2,0)
 

Desmond

New Member
Joined
Nov 8, 2005
Messages
10
Hello "Onlyadrafter".

I agree, you are the best.

Worked first time. Many, many thanks.
Des.
 

Desmond

New Member
Joined
Nov 8, 2005
Messages
10
Just one small item.

This works fine so long as the date being sought exists in the table.
Where that date is missing (not used) I had hoped the formula would have returned the result from the next younger date, what ever that might be.

ie. If I was doing a vlookup on 31/03/2006 and that date was not entered but the 15/03/2006 was, hlookup would instead use the 15/03/2006, as is the case in a normally sorted table.

Presently if the 30/03/2006 is not entered in the table, the result is #N/A.

Des.
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825

ADVERTISEMENT

If the date to be looked up is A1 and the lookup table C1:D20

=VLOOKUP(MAX((C1:C20<=A1)*(C1:C20)),C1:D20,2,0)

confirmed with CTRL+SHIFT+ENTER
 

Desmond

New Member
Joined
Nov 8, 2005
Messages
10
Hello barry houdini.

I have now converted all my sheets, and the formula appears to be functioning just as desired.

Just out of interest, in the previous response onlyadrafter utilised ", 0" at the end of the Vlookup formula. I have not found this documented, and was wondering if there were lists of other similar useful information to be found on the web. Any suggestions where I could look?

Many thanks too for your assistance.
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
I think this should be explained adequately in Excel Help

The 4th argument of VLOOKUP (the zero at the end) is the range lookup

If it's zero or FALSE then VLOOKUP will only look for an exact match, and always the first one, if there is one.

If 1 or TRUE (or omitted) then VLOOKUP can look for an approximate match (if there isn't an exact match then the greatest value less than the lookup value) but your lookup range needs to be sorted (in ascending order) for this to work correctly.

What Excel help doesn't tell you is that where the range lookup is 1, using a sorted lookup range, then if the greatest value less than or equal to the lookup value appears more than once the LAST one is matched.
 

Desmond

New Member
Joined
Nov 8, 2005
Messages
10
Thanks for the explanation. I obviously did not look close enough, or understand.

...so little time.

Regards,
Des.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,993
Messages
5,545,378
Members
410,679
Latest member
rolandbianco
Top