Search code examples
excelexcel-formulalookupindex-match

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

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
Solution

  • Try the following solution: Screenshot shown below for Example One

    enter image description here

    • 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

    enter image description here

    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))