- Microsoft Excel
- Microsoft Office
XLOOKUP?
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.
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
https://www.excelhow.net/how-to-vlookup-to-return-value-if-date-falls-between-two-dates-in-excel.html
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
Fuel-Surcharge.xlsx
Thank you for your help! The XLOOKUP formula worked, and I was able to apply it to another column with a similar situation.
Can also use standard VLOOKUP formula:
=VLOOKUP([@Date],Table1[#A
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.
=VLOOKUP([@Date],Table1[#A
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.
