We help IT Professionals succeed at work.

XLOOKUP?

Medium Priority
32 Views
Last Modified: 2020-03-23
I am trying to find an excel formula to lookup an amount in one column based on a date that is between a date range in two columns.  For example, column A has a start date, column B has an end date, and column E has the amount I need to find.  Is there a way to use XLOOKUP for this?  What would the formula look like if XLOOKUP will work?  Or do I have to use a really long nested IF statement?  I have Excel 365.
Comment
Watch Question

CERTIFIED EXPERT

Commented:
Can you set up a sample excel file for testing?
Tom FarrarConsultant
CERTIFIED EXPERT

Commented:
This link describes how to look up values based upon date ranges.  Think it might help, but may be better to see example as Saqib suggests..

https://www.excelhow.net/how-to-vlookup-to-return-value-if-date-falls-between-two-dates-in-excel.html

Author

Commented:
The sheet I am referencing is the Fuel Surcharge sheet.  On the Outside Hauling sheet, I need a formula in the "Mart FSC" column to pull the "Difference" amount that corresponds with the date in the "Date" column.  Does that make sense?
Fuel-Surcharge.xlsx
CERTIFIED EXPERT
Commented:
Try

=XLOOKUP([@Date],Table1[Week Start],Table1[Difference],,-1,1)

Author

Commented:
Thank you for your help!  The XLOOKUP formula worked, and I was able to apply it to another column with a similar situation.
Rob HensonFinance Analyst
CERTIFIED EXPERT

Commented:
Can also use standard VLOOKUP formula:

=VLOOKUP([@Date],Table1[#All],5,TRUE)

Fourth parameter in VLOOKUP allows for approximate match, in this case finds highest date that is not greater than look up value; in your example all dates are 2 March so will return value from 1 March as that is highest without going over.