Forum Discussion
Urgent Help with Excel lookup formula please
gonuegbu I started a reply saying much of what mathetes said (in particular a sample sheet would help a lot). But based on what I see in your example it looks like you want the lookup value to be displayed on the main table with the date time that has the closest match. I assumed the lookup table has unique IDs (i.e. a particular ID shows up only once in that table). It might have been easier if I could have assumed the dates and times in the main table will be in order and/or always <= to the lookup value. But without those assumptions here is my solution for you:
=LET(luValue,XLOOKUP([@ID],VlookupTable[id],VlookupTable[Vlookup Value]),IF(MIN(ABS(luValue-FILTER([Date time], [ID]=[@ID])))=ABS([@[Date time]]-luValue),luValue,""))This assumes you have a modern version of Excel with dynamic arrays and the LET function (i.e. MS 365 sub) and I made both tables formatted/defined as tables. You can see attached.
It would be far more useful if you'd post actual spreadsheets (devoid of confidential or private info). I doubt, for example, that the actual table in which you're seeking the value is only one row. And I find myself wanting to ask a number of questions. For starters see below.
A VLOOKUP formula based on an ID is easy to construct. "Closely matches" on the other hand, begs a lot of questions:
- how many different "competing" date and time values might there be in that Lookup table?
- how close is "close"?
- what if there are two matches equal distant from the search criterion, but one is behind and the other ahead, but by the same number of minutes/days?
- is there a limit beyond which it's no longer close enough, even though still the closest?
So if you could post a copy of your actual spreadsheet(s) along with answers to those questions, then I or somebody else should be able to give you the help you need.