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 is about sorting your data. I will explain the approximate lookup first.
You have the function (field names are not the official. I use these ones to help you understand)
=VLOOKUP(<key to be searched>,<table where to search>,<column number in the table>,<lookup type>)
It looks for the value in <key to be searched> looking at the first column at <table where to search>. When it is found it gives you the value in the column at position <column number in the table>
<key to be searched> can be built with more than one column.
Imagine you want to "lookup" by "Customer ID" (eg A2) and "Date" (eg B2)
You can build a composite field concatenating values
= "Cust: " & A2 & " - Date: " & B2
You have to build this both in the <key to be searched> and in the first column in the <table where to search>
In your case you would need this type of key
And now the important point:
<lookup type> has two possibilities
False or 0 = exact lookup
True or 1 = approximate
False or 0 = exact lookup.
Maybe is the one you already know.
If <key to be searched> exists in the first column at <table where to search> then it gives you the value in the column at position <column number in the table>*
If not it gives a #N/A, to tell you it is not found **.
* If there are several keys in the table matching your request, you get the first one that is found.
** BEWARE, if it is found and the column contains a #N/A for your column, you get that #N/A. To check that, you can lookup for the first column to check your are not losing your key value.
True or 1 = approximate
You need your <table where to search> sorted in descending or ascending order.
You'll have to try the order that works for you.
In this case VLOOKUP will start at row one and would do:
1) ask: Is my key greater than the value that I am reading in the table?
If not proceed to next row and repeat 1
If greater, stop and go to 2
2) retrieve value: take value in previous row an give it to my user. 🙂
So if you have
Table
| key (Cust + Date YYYYMMDD) | ID | Date | col 1 | col 2 | col 3 |
| Cust: 1 - Date: 2021-01-01 | 1 | 1-jan-2021 | a | a | a |
| Cust: 1 - Date: 2021-02-01 | 1 | 1-feb-2021 | b | b | b |
| Cust: 2 - Date: 2021-01-01 | 2 | 1-jan-2021 | c | c | c |
| Cust: 2 - Date: 2021-02-01 | 2 | 1-feb-2021 | d | d | d |
Formula
=VLOOKUP("Cust: 2 - Date: 2021-01-01";<table>,4,1)
should give you c
BEWARE. maybe you need to add some new rows for each customer to prevent taking data from previous customer. For instance:
=VLOOKUP("Cust: 2 - Date: 2020-01-01";<table>,4,1)
should give you b that comes from cust 1
You will need a line with this key for each customer:
Cust: 2 - Date: 1900-01-01
I am not solving everything here, but I wish it helps