Fast multiple criteria lookup, one criteria sorted

[rhenman]

Hi All,
I'm looking for a fast formula to perform a lookup with multiple criteria, where one of the criteria is sorted and the other is not. Any ideas? Currently I have a formula that is giving the correct results, but with large datasets it is too slow.

The spreadsheet has a raw data sheet that is arranged in batches. Each row has a unique (but not always incrementing) BatchID, a Sample Date/Time that is always incrementing (well at least is never decrementing, it is possible for there to be two identical Sample Date/Time values), and then columns with the values. I have no control over the raw data sheet.

For a given date I want to lookup the values, so I have a formatted data sheet with a row for each day, I need to lookup the raw datasheet and find the ph, flow and other values from that day (irrespective of the time of day). What is making this tricky is:
• Not all dates are present in the raw data. In this case I want to display a blank cell in the formatted data sheet
• Some dates have multiple batches. For example the pH and Flow may have been entered at different times. So the lookup needs to check not only for the correct date, but also that there is data in the desired column.

Examples:
Raw data sheet:

Problem example 2.xlsx
	A	B	C	D
1	BatchID	Sample Date/Time	pH	Flow
2	36	01/07/2013 10:30:00	6.59	254
3	39	02/07/2013 06:00:00	6.45
4	40	02/07/2013 09:30:00		236
5	41	03/07/2013 07:30:00	6.71	171
6	42	05/07/2013 08:00:00	6.35	191
7	43	06/07/2013 06:00:00	6.16	221
8	44	07/07/2013 08:00:00	6.15	233
Raw Data

Formatted data sheet

Problem example 2.xlsx
	A	B	C
1	Date	pH	Flow
2	1/07/2013	6.59	254
3	2/07/2013	6.45	236
4	3/07/2013	6.71	171
5	4/07/2013
6	5/07/2013	6.35	191
7	6/07/2013	6.16	221
8	7/07/2013	6.15	233
Formatted Data

Cell Formulas
Range	Formula
B2:C8	B2	=IFERROR(INDEX('Raw Data'!C$2:C$5000,MATCH(1,(TRUNC('Raw Data'!$B$2:$B$5000,0)=$A2)*('Raw Data'!C$2:C$5000<>""),0)),"")
A3:A8	A3	=A2+1

My current formula to lookup pH is (i.e. formula in cell B2):
=IFERROR(INDEX('Raw Data'!C$2:C$5000,MATCH(1,(TRUNC('Raw Data'!$B$2:$B$5000,0)=$A2)*('Raw Data'!C$2:C$5000<>""),0)),"")

Adding a helper column in an intermediate sheet to truncate the date/time reduced the execution time by about half. This helps, but I'm looking for about 100 x reduction in execution time.
Just looking up the date and using an approximate match gave the speed increase I'm looking for but can't handle the case of multiple batches on the same day.
Any ideas?
I'm not able to use vba/macros in the workbook.

 

[Alex Blakenburg]

See if this is any faster

20210513 Lookup DateTime Multiple Criteria.xlsx
	A	B	C	D
1	Date	pH	Flow
2	1/07/2013	6.59	254
3	2/07/2013	6.45	236
4	3/07/2013	6.71	171
5	4/07/2013
6	5/07/2013	6.35	191
7	6/07/2013	6.16	221
8	7/07/2013	6.15	233
9
Formatted Data

Cell Formulas
Range	Formula
B2:C8	B2	=XLOOKUP(1,('Raw Data'!$B$2:$B$8>=$A2)*('Raw Data'!$B$2:$B$8<($A2+1))*('Raw Data'!C$2:C$8<>""),'Raw Data'!C$2:C$8,"",0)
A3:A8	A3	=A2+1

 

[rhenman]

Thanks Alex, the formula did provide an improvement in speed, in my test of looking up 5000 dates it ran in 3.5 seconds vs 4.9 seconds (for 1 column e.g. pH). However I was hoping to get something close to the speeds you can get when data is sorted i.e. using match_type of 1 (ascending order) in a MATCH function. For reference these are running in 0.04 seconds in my dataset, however I can't work out how to get it to handle the multiple batches on one day.

 

[Fluff]

As long as you don't have a date with more than one value for pH or Flow how about

+Fluff 1.xlsm
	A	B	C
1	Date	pH	Flow
2	01/07/2013	6.59	254
3	02/07/2013	6.45	236
4	03/07/2013	6.71	171
5	04/07/2013
6	05/07/2013	6.35	191
7	06/07/2013	6.16	221
8	07/07/2013	6.15	233
9
Master

Cell Formulas
Range	Formula
B2:C2,C3:C8	B2	=FILTER('Raw Data'!C$2:C$100,(INT('Raw Data'!$B$2:$B$100)=$A2)*('Raw Data'!C$2:C$100<>""),"")
A3:A8	A3	=A2+1
B3:B8	B3	=FILTER('Raw Data'!C$2:C$100,(INT('Raw Data'!$B$2:$B$100)=A3)*('Raw Data'!C$2:C$100<>""),"")

 

[rhenman]

Thanks Fluff,
That formula provided a small improvement on Alex's, running in 3.3 seconds. Adding a helper column to pre-truncate the dates dropped it down to 2.4 seconds.
Note these are the just the times for 1 column, the spreadsheet has a few hundred columns so the time still blows out, but it is an improvement
It is possible that a date will have multiple values for pH, flow etc. I overcame this by inserting an @ at the start of the formula. (=@FILTER)

I also experimented with an INDEX,MATCH,IF formula as below. This ran in 2.8 seconds.

Problem example 2 (version 1).xlsb
	A	B	C
1	Date	pH	Flow
2	1/07/2013	6.59	254
3	2/07/2013	6.45	236
4	3/07/2013	6.71	171
5	4/07/2013
6	5/07/2013	6.35	191
7	6/07/2013	6.16	221
8	7/07/2013	6.15	233
Formatted data 2

Cell Formulas
Range	Formula
B2:C8	B2	=IFERROR(IF(INDEX(Intermediate!$A$2:$A$5000,MATCH($A2,IF('Raw Data'!C$2:C$5000<>"",Intermediate!$A$2:$A$5000),1))=$A2, INDEX('Raw Data'!C$2:C$5000,MATCH($A2,IF('Raw Data'!C$2:C$5000<>"",Intermediate!$A$2:$A$5000),1)),""),"")
A3:A8	A3	=A2+1

 

[Peter_SSs]

It is possible that a date will have multiple values for pH, flow

In that circumstance, which of the multiple values do you want returned?

Do you have any times at or very close to midnight? If exactly midnight is possible, can you confirm which day you want that row included in?
I ask that because when I was doing some experimenting with midnight times involved, INT and TRUNC were, as one example, returning 26/08/2013 when the cell was showing "27/08/2013 00:00:00"

 

[rhenman]

In that circumstance, which of the multiple values do you want returned?

Do you have any times at or very close to midnight? If exactly midnight is possible, can you confirm which day you want that row included in?
I ask that because when I was doing some experimenting with midnight times involved, INT and TRUNC were, as one example, returning 26/08/2013 when the cell was showing "27/08/2013 00:00:00"

It would be rare that there are multiple values on one date, in the vast majority of cases there would just be the one. On the odd occasion it does happen the preference would be to display the last value, though the first value would also be acceptable.

Exactly midnight, as recorded in the spreadsheet, would be possible. Say a measurement was taken on 27/8/2013 without the time noted the value in the spreadsheet would be "27/08/2013 00:00:00" and should be recorded in the row for 27/8/2013. Close to midnight, but not exactly midnight is theoretically possible, though hasn't been seen in the data so far.

 

[Alex Blakenburg]

Note these are the just the times for 1 column, the spreadsheet has a few hundred columns so the time still blows out,

Would we be better off looking at a Power Query solution ?