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;
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
Please help thanks;
id Vlookup Value 1035 3/19/2021 20:51:56
Last edited by chytechp; 05-17-2021 at 01:29 PM.
Can you provide a sample file.. that shows what you expect to get back?
Are you asking to search your Main table using the VLOOKUP table value and have it return which one? are you wanting to search based on date and return the ID that has the closest match to that date/time?
What if there are 2 id's that are close matches? which do you prefer returned? is there a threshold you are ok with? meaning how much time (minutes/seconds or hours) do they have to be within to be considered a match and return?
If you find the suggestion or solution helpful, please consider adding reputation to the post.
I have attached the excel file.
So with VLOOKUP(C5,B12:B12,1,TRUE) am able to get the datetime closest the value am looking for. But i want to be able to also make it consider the ID. to be sure i return the exact result.
You could try =XLOOKUP(B12,B4:B7,A4:B7,"NA",-1)
This would return the ID and date that is closest to the date provided.
Don't tell us that it is URGENT, please. To you it may be, but to us it isn’t. If it’s urgent, you should be calling on a consultant, not a volunteer who is giving their free time.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
Thanks AliGw. I have edited the title.
Thanks Cubangt, this is very close.
is there something its not returning or not as expected?Originally Posted by chytechp
I want it to return the datetime that is closest to one on the main table and not the ID. It will be great if it can be tweaked.
All you would need to do is change it to this;
=XLOOKUP(B12,B4:B7,B4:B7,"NA",-1)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks