Spreadsheet: 779485d1651812985-how-to-lookup-only-values-that-meet-criteria-within-a-subset-of-data-example-file
Sheet: Sheet1

Employee ID | Date Entered | Positive? Y/N | Previous Close Contact? Y/N |  | Employee ID | Exposure Date |  | Notes |  |  |  |  | Helper Column 1 | Helper Column 2 | Helper Column 4
PA489 | 2022-01-01 00:00:00 | NO |  |  | JA712 | 2021-12-25 00:00:00 |  | Excel [NAME] Is This Exactly:
[NAME] | 2022-01-02 00:00:00 | YES |  |  | YT430 | 2021-12-27 00:00:00 |  | =IF(AND(0<=(B2-XLOOKUP(A2,$F$2:$F$127,$G$2:$G$127,0,0,1)),(B2-XLOOKUP(A2,$F$2:$F$127,$G$2:$G$127,0,0,1)<=14)),"YES","")
DM828 | 2022-01-02 00:00:00 | YES |  |  | EC729 | 2021-12-28 00:00:00
EH844 | 2022-01-02 00:00:00 | YES |  |  | DT764 | 2021-12-29 00:00:00 |  | Helper columns on right to show order of events (returning exposure date from [NAME], subtracting exposure date from entered date,
KJ864 | 2022-01-02 00:00:00 | YES |  |  | KJ864 | 2021-12-31 00:00:00 |  | and applying if conditionals to determine if EMPID was listed as close contact)
[NAME] | 2022-01-02 00:00:00 | YES |  |  | [NAME] | 2022-01-01 00:00:00
CF216 | 2022-01-03 00:00:00 | YES |  |  | LM204 | 2022-01-01 00:00:00 |  | Questions
ED795 | 2022-01-03 00:00:00 | YES |  |  | PA489 | 2022-01-01 00:00:00 |  | How to perform xlookup only within the subset of data where the exposure date is <=14 days old?
JV939 | 2022-01-03 00:00:00 | YES |  |  | [NAME] | 2022-01-02 00:00:00 |  | *Please refer to yellow highlighted cell on bottom on left table
SM171 | 2022-01-03 00:00:00 | YES |  |  | SM326 | 2022-01-02 00:00:00
SP470 | 2022-01-03 00:00:00 | NO |  |  | [NAME] | 2022-01-03 00:00:00 |  | If not possible, is there any other way to do this?
[NAME] | 2022-01-03 00:00:00 | YES |  |  | TH113 | 2022-01-03 00:00:00
WF404 | 2022-01-03 00:00:00 | YES |  |  | [NAME] | 2022-01-04 00:00:00 |  | Issues
DC142 | 2022-01-04 00:00:00 | YES |  |  | BJ903 | 2022-01-04 00:00:00 |  | Issue 1: | when same EMPID is reported multiple times, vlookup
NP590 | 2022-01-04 00:00:00 | YES |  |  | CJ255 | 2022-01-04 00:00:00 |  |  | only returns 1st instance, so all subsequent positive
VN852 | 2022-01-04 00:00:00 | YES |  |  | CL936 | 2022-01-04 00:00:00 |  |  | entries aren't being accounted for
RA830 | 2022-01-05 00:00:00 | YES |  |  | DR677 | 2022-01-04 00:00:00
CM502 | 2022-01-06 00:00:00 | YES |  |  | JS786 | 2022-01-04 00:00:00 |  | Issue 2: | every close contact (cc) is recorded on the right, but only
JE651 | 2022-01-06 00:00:00 | YES |  |  | SN579 | 2022-01-04 00:00:00 |  |  | employees who either test (negative or positive) or
JV596 | 2022-01-06 00:00:00 | YES |  |  | [NAME] | 2022-01-05 00:00:00 |  |  | are reporting they had close proximity with confirmed positive
[NAME] | 2022-01-06 00:00:00 | YES |  |  | MV632 | 2022-01-06 00:00:00 |  |  | case (whether another employee or not) are recorded in the
NM900 | 2022-01-06 00:00:00 | YES |  |  | JN399 | 2022-01-07 00:00:00 |  |  | left table, so if multiple employees sit next to each other
AF826 | 2022-01-07 00:00:00 | YES |  |  | [NAME] | 2022-01-08 00:00:00 |  |  | and they all list each other, each emp will have a cc record
[NAME] | 2022-01-07 00:00:00 | YES |  |  | RB400 | 2022-01-08 00:00:00 |  |  | for everyone who listed them, causing huge buildup.
JH223 | 2022-01-07 00:00:00 | YES |  |  | SH139 | 2022-01-08 00:00:00
JW200 | 2022-01-07 00:00:00 | YES |  |  | EG231 | 2022-01-11 00:00:00 |  |  | If there's a better solution to this issue, please advise as
MD986 | 2022-01-07 00:00:00 | YES |  |  | [NAME] | 2022-01-11 00:00:00 |  |  | manually double-checking every record to ensure same person
CM265 | 2022-01-08 00:00:00 | YES |  |  | SM284 | 2022-01-11 00:00:00 |  |  | isn't listed multiple times for same exposure instance would
JB979 | 2022-01-08 00:00:00 | YES |  |  | [NAME] | 2022-01-12 00:00:00 |  |  | be taxing
TL967 | 2022-01-08 00:00:00 | YES |  |  | [NAME] | 2022-01-13 00:00:00
AW655 | 2022-01-09 00:00:00 | YES |  |  | [NAME] | 2022-01-13 00:00:00 |  | Comments
PJ828 | 2022-01-09 00:00:00 | YES |  |  | WC303 | 2022-01-13 00:00:00
ER514 | 2022-01-10 00:00:00 | YES |  |  | KL187 | 2022-01-14 00:00:00
RG231 | 2022-01-10 00:00:00 | YES |  |  | LK582 | 2022-01-14 00:00:00
SM745 | 2022-01-10 00:00:00 | YES |  |  | VP268 | 2022-01-14 00:00:00
[NAME] | 2022-01-11 00:00:00 | YES |  |  | [NAME] | 2022-01-15 00:00:00
RM428 | 2022-01-11 00:00:00 | YES |  |  | SJ157 | 2022-01-15 00:00:00
SC440 | 2022-01-11 00:00:00 | NO |  |  | MM499 | 2022-01-16 00:00:00
SW620 | 2022-01-11 00:00:00 | YES |  |  | HE905 | 2022-01-17 00:00:00
VL611 | 2022-01-11 00:00:00 | YES |  |  | MW624 | 2022-01-17 00:00:00
MB674 | 2022-01-12 00:00:00 | YES |  |  | [NAME] | 2022-01-19 00:00:00
AF240 | 2022-01-13 00:00:00 | YES |  |  | [NAME] | 2022-01-19 00:00:00
MW605 | 2022-01-13 00:00:00 | YES |  |  | [NAME] | 2022-01-20 00:00:00
LG847 | 2022-01-14 00:00:00 | YES |  |  | RS812 | 2022-01-20 00:00:00
WN455 | 2022-01-14 00:00:00 | YES |  |  | SW620 | 2022-01-20 00:00:00
NW521 | 2022-01-15 00:00:00 | YES |  |  | AG769 | 2022-01-21 00:00:00
VM182 | 2022-01-15 00:00:00 | YES |  |  | PW310 | 2022-01-21 00:00:00
[NAME] | 2022-01-16 00:00:00 | YES |  |  | AF826 | 2022-01-22 00:00:00
CF675 | 2022-01-17 00:00:00 | YES |  |  | ML824 | 2022-01-22 00:00:00
JD429 | 2022-01-17 00:00:00 | YES |  |  | CW689 | 2022-01-23 00:00:00
[NAME] | 2022-01-17 00:00:00 | YES |  |  | NR371 | 2022-01-23 00:00:00
RW309 | 2022-01-17 00:00:00 | NO |  |  | AC862 | 2022-01-24 00:00:00
SB549 | 2022-01-17 00:00:00 | YES |  |  | CJ153 | 2022-01-24 00:00:00
VR368 | 2022-01-17 00:00:00 | NO |  |  | DP993 | 2022-01-24 00:00:00
JB432 | 2022-01-18 00:00:00 | YES |  |  | JG622 | 2022-01-24 00:00:00
PS212 | 2022-01-18 00:00:00 | YES |  |  | CC758 | 2022-01-25 00:00:00
BG652 | 2022-01-19 00:00:00 | YES |  |  | DL645 | 2022-01-25 00:00:00
LS780 | 2022-01-19 00:00:00 | YES |  |  | KH176 | 2022-01-25 00:00:00
SG692 | 2022-01-19 00:00:00 | YES |  |  | WT464 | 2022-01-25 00:00:00
TH489 | 2022-01-19 00:00:00 | YES |  |  | CD327 | 2022-01-26 00:00:00
BA786 | 2022-01-21 00:00:00 | YES |  |  | CH830 | 2022-01-26 00:00:00
CR938 | 2022-01-21 00:00:00 | YES |  |  | JF279 | 2022-01-26 00:00:00
JM346 | 2022-01-21 00:00:00 | YES |  |  | RA940 | 2022-01-26 00:00:00
SH525 | 2022-01-21 00:00:00 | YES |  |  | CJ857 | 2022-01-27 00:00:00
[NAME] | 2022-01-22 00:00:00 | YES |  |  | RG601 | 2022-01-27 00:00:00
AH236 | 2022-01-23 00:00:00 | NO |  |  | RH265 | 2022-01-27 00:00:00
DA891 | 2022-01-23 00:00:00 | YES |  |  | [NAME] | 2022-01-28 00:00:00
JP891 | 2022-01-23 00:00:00 | YES |  |  | SM745 | 2022-01-28 00:00:00
JS572 | 2022-01-23 00:00:00 | YES |  |  | CW867 | 2022-01-29 00:00:00
DG628 | 2022-01-24 00:00:00 | YES |  |  | BL241 | 2022-01-31 00:00:00
ML188 | 2022-01-24 00:00:00 | YES |  |  | DF428 | 2022-01-31 00:00:00
ML246 | 2022-01-24 00:00:00 | YES |  |  | JE440 | 2022-02-01 00:00:00
[NAME] | 2022-01-25 00:00:00 | YES |  |  | RW144 | 2022-02-01 00:00:00
EE930 | 2022-01-25 00:00:00 | YES |  |  | DM202 | 2022-02-02 00:00:00
[NAME] | 2022-01-25 00:00:00 | NO |  |  | MH493 | 2022-02-02 00:00:00
LB531 | 2022-01-25 00:00:00 | YES |  |  | [NAME] | 2022-02-03 00:00:00
[NAME] | 2022-01-25 00:00:00 | YES |  |  | RG814 | 2022-02-03 00:00:00
AJ817 | 2022-01-26 00:00:00 | YES |  |  | SK819 | 2022-02-03 00:00:00
CL936 | 2022-01-27 00:00:00 | YES |  |  | TK515 | 2022-02-03 00:00:00
[NAME] | 2022-01-27 00:00:00 | YES |  |  | LP446 | 2022-02-04 00:00:00
TC416 | 2022-01-28 00:00:00 | YES |  |  | CN819 | 2022-02-05 00:00:00
KA199 | 2022-01-29 00:00:00 | YES |  |  | JR222 | 2022-02-05 00:00:00
AS865 | 2022-01-30 00:00:00 | YES |  |  | [NAME] | 2022-02-05 00:00:00
FL614 | 2022-01-30 00:00:00 | YES |  |  | [NAME] | 2022-02-05 00:00:00
LM195 | 2022-01-30 00:00:00 | YES |  |  | TZ361 | 2022-02-05 00:00:00
MP267 | 2022-01-30 00:00:00 | YES |  |  | CJ193 | 2022-02-06 00:00:00
SM284 | 2022-01-30 00:00:00 | YES |  |  | WP211 | 2022-02-06 00:00:00
[NAME] | 2022-01-31 00:00:00 | YES |  |  | JH410 | 2022-02-07 00:00:00
DM353 | 2022-01-31 00:00:00 | NO |  |  | KB584 | 2022-02-07 00:00:00
YT430 | 2022-01-31 00:00:00 | YES |  |  | RD472 | 2022-02-07 00:00:00
[NAME] | 2022-02-13 00:00:00 | YES |  |  | SB889 | 2022-02-07 00:00:00
 |  |  |  |  | SS781 | 2022-02-07 00:00:00
 |  |  |  |  | DM353 | 2022-02-08 00:00:00
 |  |  |  |  | KC825 | 2022-02-08 00:00:00
 |  |  |  |  | [NAME] | 2022-02-08 00:00:00
 |  |  |  |  | AS104 | 2022-02-09 00:00:00
 |  |  |  |  | CG257 | 2022-02-11 00:00:00
 |  |  |  |  | CG465 | 2022-02-12 00:00:00
 |  |  |  |  | DA208 | 2022-02-12 00:00:00
 |  |  |  |  | [NAME] | 2022-02-12 00:00:00
 |  |  |  |  | TB306 | 2022-02-12 00:00:00
 |  |  |  |  | BS676 | 2022-02-13 00:00:00
 |  |  |  |  | [NAME] | 2022-02-13 00:00:00
 |  |  |  |  | SM828 | 2022-02-13 00:00:00
 |  |  |  |  | TB394 | 2022-02-13 00:00:00
 |  |  |  |  | MC295 | 2022-02-14 00:00:00
 |  |  |  |  | [NAME] | 2022-02-15 00:00:00
 |  |  |  |  | [NAME] | 2022-02-15 00:00:00
 |  |  |  |  | NM900 | 2022-02-15 00:00:00
 |  |  |  |  | SW114 | 2022-02-16 00:00:00
 |  |  |  |  | RM514 | 2022-02-17 00:00:00
 |  |  |  |  | NH333 | 2022-02-19 00:00:00
 |  |  |  |  | [NAME] | 2022-02-19 00:00:00
 |  |  |  |  | SM745 | 2022-02-19 00:00:00
 |  |  |  |  | TH489 | 2022-02-19 00:00:00
 |  |  |  |  | WG609 | 2022-02-19 00:00:00
 |  |  |  |  | AC680 | 2022-02-22 00:00:00
 |  |  |  |  | EN662 | 2022-02-22 00:00:00
 |  |  |  |  | JB816 | 2022-02-23 00:00:00
 |  |  |  |  | BN173 | 2022-02-24 00:00:00
 |  |  |  |  | EF676 | 2022-02-24 00:00:00
 |  |  |  |  | GH471 | 2022-02-25 00:00:00
 |  |  |  |  | EM842 | 2022-02-26 00:00:00
 |  |  |  |  | TW124 | 2022-02-26 00:00:00
 |  |  |  |  | [NAME] | 2022-02-27 00:00:00
 |  |  |  |  | LW276 | 2022-02-27 00:00:00
 |  |  |  |  | RW241 | 2022-02-27 00:00:00
 |  |  |  |  | JL112 | 2022-02-28 00:00:00