2-factor lookup with an approximate match in Excel (not 365 version)?

Question

I have a timecard-style task where I need to analyze a large group of data to evaluate maximum/minimum occupancy for a controlled-entry worksite. I get data in a bulk raw format. There are many columns, but the pertinent ones are Name, Badge scan time, and scan location (east/west, in/out).

My task is to construct a lookup that finds the last time an employee scanned their badge and determine if it was in or out of the controlled space.

I sort the data ascending by scan time, and I can both find the combination of a selected employee and the scan-location (general space vs controlled space) and scan-type (in/out) using concatenation. Independently, I can find the last scan time of a badge based on selected inputs (I have fields for year, month, day, and time of day) using the following formula:

=INDEX(BadgeTime,MATCH(TRUNC(DATE($B$3,$C$3,$D$3)+$F5,6),BadgeTime,1),1)

I added a helper column in the raw data that truncates the badge scan time to match my lookup. (That helper column is the named range "BadgeTime" in the formula).

My question is how do I combine the two? Every version of two-factor lookup I have found so far uses (needs) exact match because they all use some version of concatenation (or they create 2 arrays of true/false, force it to 1/0, and compare). But I need to find the closest (approximate) time of a specific (exact) name and place.

[All the examples I've found are along the lines of "how to look up name and month to find sales data". When my version would be "how to look up name and sales target data and find the last month that person met the target"].

I found this formula from an answer in a different question and am curious if I can somehow incorporate something similar:

=MAX(($A$2:$A$101*100+$B$1:$CW$1<B103)*($B$2:$CW$101=TargetValue)*($A$2:$A$101*100+$B$1:$CW$1))

This is from the first answer in the question HERE

Based on feedback (thank you), here is a table of sample data.
Example 1: Lookup for Harmony Song on 2021-11-01 at 06:45 would find Controlled IN at 44501.275115.

Example 2: Lookup for Harmony Song on 2021-11-01 at 06:30 would skip the Lobby IN and Lobby OUT and find Controlled OUT at 44501.269965.

A	B	C	D	E
Full Name	Employee No.	Badge Date/Time	BadgeTime	Location
John Smith	552510	2021-11-01 05:31:02	44501.229884	Lobby IN
Matthew Doe	321321	2021-11-01 05:37:57	44501.234687	Lobby IN
Matthew Doe	321321	2021-11-01 05:40:22	44501.236365	Office
Matthew Doe	321321	2021-11-01 05:41:27	44501.237118	Office
Daryl Strawberry	311223	2021-11-01 05:59:26	44501.249606	Lobby IN
Jerry Markson	561100	2021-11-01 06:07:26	44501.255162	Lobby IN
Donna Matthews	571050	2021-11-01 06:13:41	44501.259502	Lobby IN
Terry McNeil	255103	2021-11-01 06:13:51	44501.259618	Lobby IN
Terrance Covington	625825	2021-11-01 06:14:42	44501.260208	Lobby IN
Terry McNeil	255103	2021-11-01 06:18:21	44501.262743	Office
Ronald McDonald	201202	2021-11-01 06:23:47	44501.266516	Lobby IN
Ronald McDonald	201202	2021-11-01 06:25:10	44501.267476	Office
Jerry Maguire	561390	2021-11-01 06:26:16	44501.268240	Lobby IN
Cheryl Gates	111234	2021-11-01 06:27:34	44501.269143	Lobby IN
Harmony Song	111234	2021-11-01 06:28:45	44501.269965	Controlled OUT
Harmony Song	111555	2021-11-01 06:29:27	44501.270451	Lobby OUT
Harmony Song	111555	2021-11-01 06:31:03	44501.271562	Lobby IN
John Smith	552510	2021-11-01 06:31:07	44501.271608	Controlled IN
Franklin Stevens	551625	2021-11-01 06:31:40	44501.271990	Lobby IN
Terrance Covington	625825	2021-11-01 06:32:25	44501.272511	Controlled IN
Ronald McDonald	201202	2021-11-01 06:33:55	44501.273553	Controlled IN
Harmony Song	111555	2021-11-01 06:36:10	44501.275115	Controlled IN
Cheryl Gates	111234	2021-11-01 06:36:28	44501.275324	Controlled IN
Donna Matthews	571050	2021-11-01 06:36:49	44501.275567	Controlled IN

ANSWER

Here is a table of example results based on the accepted answer below (posted here for formatting), with columns indicated to match the answer formula.

G	H	I	K
Lookup Date	Lookup Time	Harmony Song	John Smith
2021-11-01	06:28	Not Present	Not Present
2021-11-01	06:29	44501.269965	Not Present
2021-11-01	06:30	44501.269965	Not Present
2021-11-01	06:31	44501.269965	Not Present
2021-11-01	06:32	44501.269965	44501.271608
2021-11-01	06:33	44501.269965	44501.271608
2021-11-01	06:34	44501.269965	44501.271608
2021-11-01	06:35	44501.269965	44501.271608

It would help if you would mock up a small table (only include what is needed) of test data and show the inputs and expected outputs of that small test data set. Please do this as a mark down table that we can copy paste. — Scott Craner, Aug 24 '23 at 13:30
@ScottCraner, I will create a sample set of data. But, I don't ask many questions here, can you please describe exactly what you mean/expect when you ask for a "mark down table"? — Matthew Sechrist, Aug 24 '23 at 13:55
I don't see `Harmony Song on 2021-11-01 at 6:45` in the data? — Mayukh Bhattacharya, Aug 24 '23 at 14:50
@MayukhBhattacharya, yes, that is the dilemma. The search will be for a specific time and it needs to find the next-closest time (since a person entering/leaving a place is always variable). — Matthew Sechrist, Aug 24 '23 at 14:56
and we always skip the lobby? Or is that specific to test 2? — Scott Craner, Aug 24 '23 at 14:59
Its simple then use this formula: `=INDEX(D:D,MATCH(2,1/((G2=A:A)*((H2+I2)>=C:C)),1))` — Mayukh Bhattacharya, Aug 24 '23 at 15:03
@ScottCraner yes, always skip the Lobby (and/or Office). The only data needed is the IN/OUT of the Controlled space. — Matthew Sechrist, Aug 24 '23 at 15:03
@MayukhBhattacharya This gets me SO CLOSE! Your formula is solving the problem of finding the next closest time for a specific person. The last thing I need to do is skip the lines that are not the Controlled IN/OUT. — Matthew Sechrist, Aug 24 '23 at 15:23
@MatthewSechrist is it working on your end? Can you confirm? — Mayukh Bhattacharya, Aug 24 '23 at 15:47
@MayukhBhattacharya I have added the formula updates into my actual data and so far it's still not skipping the undesired rows. However, I am currently unable to see the images you posted that may help. My company's security policy is preventing the images. — Matthew Sechrist, Aug 24 '23 at 15:52
Oh okay, this is the formula: `=INDEX($D:$E,MATCH(2,1/(($G2=$A:$A)*(($H2+$I2)>=$C:$C)*($E:$E<>"Lobby OUT")*($E:$E<>"Lobby IN")*($E:$E<>"Office")),1),COLUMN(A1))` where `G1` --> `Harmony Song` , `H1` --> `11/1/2021` and `E1` you can enter either `6:30:00` or `6:45:00` also the formula need to be entered using `CTRL`+`SHIFT`+`ENTER` — Mayukh Bhattacharya, Aug 24 '23 at 15:54
@MayukhBhattacharya What is the basis of the lookup in INDEX changing from D:D to D:E? Is it related to the COLUMN(A1) at the end? What is COLUMN(A1) looking for and then sending into INDEX? — Matthew Sechrist, Aug 24 '23 at 15:54
`Column D` refers to `Badge Time` while `Column E` refers to `Locations` so when you drag right the `COLUMN(A1)` changes to `COLUMN(B1)` i.e. it is changing from 1 to 2 where 1 is my first column and 2 is my second column — Mayukh Bhattacharya, Aug 24 '23 at 15:56

Mayukh Bhattacharya · Accepted Answer · 2023-08-24T15:27:33.110

1

Try the following solution: Screenshot shown below for Example One

• Formula used in cell G4

=INDEX(D:E,MATCH(2,1/((G2=A:A)*((H2+I2)>=C:C)),1),COLUMN(A1))

Note since its an array formula so while exiting the editing mode hit CTRL+SHIFT+ENTER

Updated as mentioned in comments by Scott Craner Sir. Instead of using {1,2} use the one updated to drag over.

Screenshot shown below for Example TWO

Since you need to exclude the Office , Lobby IN & Lobby OUT use the following formula:

=INDEX($D:$E,
  MATCH(2,1/(($G2=$A:$A)*(($H2+$I2)>=$C:$C)*($E:$E<>"Lobby OUT")*($E:$E<>"Lobby IN")*($E:$E<>"Office")),1),
  COLUMN(A1))

edited Aug 24 '23 at 15:27

answered Aug 24 '23 at 15:05

Mayukh Bhattacharya

12,541
5
21
32

1

This works! I did make some changes based on the arrangement of the results table, which I will post as well. Here is the final version of the formula based on the above answer: `=IFNA(INDEX($D:$D,MATCH(2,1/((I$2=$A:$A)*(($G3+$H3)>=$C:$C)*($E:$E<>"Lobby IN")*($E:$E<>"Lobby OUT")*($E:$E<>"Office")),1)),"Not Present")` – Matthew Sechrist Aug 24 '23 at 17:03
@MatthewSechrist Sounds great, glad it worked for you =) – Mayukh Bhattacharya Aug 24 '23 at 17:04
1

| **G** | **H** | **I** | **K** | |:----------:|:-----:|:------------:|:------------:| | Date | Time | Harmony Song | John Smith | | 2021-11-01 | 06:28 | Not Present | Not Present | | 2021-11-01 | 06:29 | 44501.269965 | Not Present | | 2021-11-01 | 06:30 | 44501.269965 | Not Present | | 2021-11-01 | 06:31 | 44501.269965 | Not Present | | 2021-11-01 | 06:32 | 44501.269965 | 44501.271608 | | 2021-11-01 | 06:33 | 44501.269965 | 44501.271608 | | 2021-11-01 | 06:34 | 44501.269965 | 44501.271608 | | 2021-11-01 | 06:35 | 44501.269965 | 44501.271608 | – Matthew Sechrist Aug 24 '23 at 17:12

2-factor lookup with an approximate match in Excel (not 365 version)?

1 Answers1