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.
mtarler The Lookup date IDs are not unique, they appear on multiple dates and times. So I want to be able to match each with the nearest value on the main table.
This is the only thing left I need, please.
gonuegbu So you want every value in the main table to have the closest value in the lookup table? I did that in the attached. But if that isn't what you need, I go back to the original request for more information like a sheet with sample data (not just 1 point) and examples of what you would expect/want to be found. So like a dozen or so sample values in each table with at least a few examples of how the vlookup column should look like.
