Reverse search using lookup / vlookup

[Desmond]

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.

 

[onlyadrafter]

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]

Hello "Onlyadrafter".

I agree, you are the best.

Worked first time. Many, many thanks.
Des.

 

[Desmond]

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]

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]

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]

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]

Thanks for the explanation. I obviously did not look close enough, or understand.

...so little time.

Regards,
Des.