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.
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

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.
 

Forum statistics

Threads
1,141,030
Messages
5,703,829
Members
421,318
Latest member
cg_cartoonexcel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top