Best way to do a Lookup on a variable date range

Occasional Contributor



need help 


I want to XLookup based on a product code to give me a retail price

current formula =VLOOKUP(O7,Pricefile!E:F,2,0)


- now the problem i need help is with is that i want my lookup above to only give the product code price based on the dispatch date (column M in pic 1) based on this date falling between relevant ranges in columns B (start date of price file) and C (end of price file) 


what's my best forumal for this - do i need an IFS foumla in front of my lookup?




1 Reply



Those images are only partly helpful (far too much in them so even with a reasonably large screen, they still show quite small on the screen) And It's not totally clear what the relationship is between the two images.


That aside, if you have a relatively new version of Excel, the FILTER function most likely would give you the results you're looking for. You can enter multiple criteria (so product code AND start date AND end date)...


Here's a helpful video intro to the FILTER function.