Urgent Help with Excel lookup formula please

[gonuegbu]

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;

[#2]

@gonuegbu 

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.

[#3 · Accepted solution]

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

[#4]

@gonuegbu 

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

[#5]

@mtarler   WOOW, you are genius, this is something i spent the whole weekend figuring out and not winning ..... thanks so much.

[#6]

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

[#7]

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