SOLVED

Look up to match dates and data

Occasional Contributor

In one workbook I have several induvial data sheets and one “Master” sheet.


Col A of the Master sheet lists > 1 year of Date & Time in 5 minute contiguous steps.


Each data sheet has Col C as Date & Time in 5 minute stamps which are sequential but not necessarily contiguous, Cols AS & AT may contain “results” for the respective sheet Col C Date & Time.


I wish to procure a lookup formula which will place each data sheets AS & AT data into new columns in the Master where the Date & Time stamps match.


Thank you, Bebbspoke

2 Replies

I would try with Index match

https://support.office.com/en-US/article/Look-up-values-with-VLOOKUP-INDEX-or-MATCH-68297403-7C3C-41...

 

@Johnnie Thomas your the King of formuales, what would you do?

best response confirmed by Olaf Hubel (Microsoft)
Solution

I agree with Olaf's suggestion you can use the MATCH formula to find Rows on your Data Sheet where there are time stamp matches.  You can use the Index formula to grab a value from your Data columns.

 

Combine them and you get the type of lookup it sounds like you need

 

the psudeo formula is:

 

=INDEX(Data Column in Data Sheet, MATCH(Time Stamp in Master Sheet Timestamp Column, TimeStamp Column in Data Sheet, 0))....the zero means look for exact matches

 

Given the layout of your data you could also the VLOOKUP formula, but the INDEX/MATCH combination runs faster and is a valuable, versitile function combo to learn.

We support Ukraine and condemn war. Push Russian government to act against war. Be brave, vocal and show your support to Ukraine. Follow the latest news HERE