Fast multiple criteria lookup, one criteria sorted

rhenman

New Member
Joined
May 13, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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
ABCD
1BatchIDSample Date/TimepHFlow
23601/07/2013 10:30:006.59254
33902/07/2013 06:00:006.45
44002/07/2013 09:30:00236
54103/07/2013 07:30:006.71171
64205/07/2013 08:00:006.35191
74306/07/2013 06:00:006.16221
84407/07/2013 08:00:006.15233
Raw Data


Formatted data sheet
Cell Formulas
RangeFormula
B2:C8B2=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:A8A3=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.
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
1,153
Office Version
  1. 365
Platform
  1. Windows

rhenman

New Member
Joined
May 13, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
60,131
Office Version
  1. 365
Platform
  1. Windows

rhenman

New Member
Joined
May 13, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
ABC
1DatepHFlow
21/07/20136.59254
32/07/20136.45236
43/07/20136.71171
54/07/2013  
65/07/20136.35191
76/07/20136.16221
87/07/20136.15233
Formatted data 2
Cell Formulas
RangeFormula
B2:C8B2=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:A8A3=A2+1
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,456
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
May 13, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,136,794
Messages
5,677,779
Members
419,720
Latest member
kurman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top