Forum Discussion
Urgent Help with Excel lookup formula please
Please sir / Madam,
I need urgent help with the below;
I have two tables. I want to be able to search for the ID from a lookup table and return the one that closely matches the date and time of the main table.
Please see sample tables below; Formula is needed in the Vlookup column (main table)
Main table
| ID | Date time | Vlookup Value |
| 1035 | 3/14/2021 20:51:56 | |
| 1035 | 3/19/2021 18:30:54 | 3/19/2021 20:51:56 |
| 1090 | 3/14/2021 18:24:19 | |
| 1090 | 3/14/2021 17:58:31 |
Vlookup Table
| id | Vlookup Value |
| 1035 | 3/19/2021 20:51:56 |
Please help thanks;
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.
6 Replies
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
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 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.
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.
