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.
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.
