提问人：Matthew Sechrist 提问时间：8/24/2023 最后编辑：Mayukh BhattacharyaMatthew Sechrist 更新时间：8/25/2023 访问量：62
在 Excel（不是 365 版本）中具有近似匹配的 2 因素查找？
2-factor lookup with an approximate match in Excel (not 365 version)?
问：
我有一个考勤卡式的任务，我需要分析大量数据，以评估受控进入工作场所的最大/最小占用率。我以批量原始格式获取数据。有许多列，但相关的列是名称、徽章扫描时间和扫描位置（东/西、进/出）。
我的任务是构建一个查找功能，以查找员工上次扫描其徽章的时间，并确定它是在受控空间内还是在受控空间外。
我按扫描时间对数据进行升序排序，并且我可以使用串联找到所选员工和扫描位置（一般空间与受控空间）和扫描类型（输入/输出）的组合。 独立地，我可以使用以下公式根据所选输入（我有年、月、日和一天中的时间字段）找到徽章的上次扫描时间：
=INDEX(BadgeTime,MATCH(TRUNC(DATE($B$3,$C$3,$D$3)+$F5,6),BadgeTime,1),1)
我在原始数据中添加了一个帮助程序列，该列会截断锁屏提醒扫描时间以匹配我的查找。（该帮助程序列是公式中的命名范围“BadgeTime”）。
我的问题是我如何将两者结合起来？到目前为止，我发现的每个版本的双因素查找都使用（需要）精确匹配，因为它们都使用某种版本的串联（或者它们创建 2 个真/假数组，强制它为 1/0，然后进行比较）。但是我需要找到特定（确切）名称和地点的最接近（大致）时间。
[我找到的所有示例都与“如何查找名称和月份以查找销售数据”有关。当我的版本是“如何查找姓名和销售目标数据并找到该人达到目标的最后一个月”时。
我从另一个问题的答案中找到了这个公式，我很好奇我是否可以以某种方式合并类似的东西：
=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))
这是来自问题的第一个答案 这里
根据反馈（谢谢），下面是一个示例数据表。
示例 1：在 2021-11-01 的 06：45 查找 Harmony Song，会在 44501.275115 找到 Controlled IN。
示例 2：在 2021-11-01 的 06：30 查找 Harmony Song 将跳过 Lobby IN 和 Lobby OUT，并在 44501.269965 找到 Controlled OUT。
| 一个 | B | C | D | E |
|---|---|---|---|---|
| 全名 | 员工编号 | 徽章日期/时间 | 徽章时间 | 位置 |
| 约翰·史密斯 | 552510 | 2021-11-01 05:31:02 | 44501.229884 | 大堂 IN |
| 马修·多伊 | 321321 | 2021-11-01 05:37:57 | 44501.234687 | 大堂 IN |
| 马修·多伊 | 321321 | 2021-11-01 05:40:22 | 44501.236365 | 办公室 |
| 马修·多伊 | 321321 | 2021-11-01 05:41:27 | 44501.237118 | 办公室 |
| 达里尔草莓 | 311223 | 2021-11-01 05:59:26 | 44501.249606 | 大堂 IN |
| 杰里·马克森 | 561100 | 2021-11-01 06:07:26 | 44501.255162 | 大堂 IN |
| 唐娜·马修斯 | 571050 | 2021-11-01 06:13:41 | 44501.259502 | 大堂 IN |
| 特里·麦克尼尔 | 255103 | 2021-11-01 06:13:51 | 44501.259618 | 大堂 IN |
| 特伦斯·科文顿 | 625825 | 2021-11-01 06:14:42 | 44501.260208 | 大堂 IN |
| 特里·麦克尼尔 | 255103 | 2021-11-01 06:18:21 | 44501.262743 | 办公室 |
| 麦当劳叔叔 | 201202 | 2021-11-01 06:23:47 | 44501.266516 | 大堂 IN |
| 麦当劳叔叔 | 201202 | 2021-11-01 06:25:10 | 44501.267476 | 办公室 |
| 杰里·马奎尔 | 561390 | 2021-11-01 06:26:16 | 44501.268240 | 大堂 IN |
| 谢丽尔·盖茨 | 111234 | 2021-11-01 06:27:34 | 44501.269143 | 大堂 IN |
| 和声之歌 | 111234 | 2021-11-01 06:28:45 | 44501.269965 | 受控输出 |
| 和声之歌 | 111555 | 2021-11-01 06:29:27 | 44501.270451 | 大堂 OUT |
| 和声之歌 | 111555 | 2021-11-01 06:31:03 | 44501.271562 | 大堂 IN |
| 约翰·史密斯 | 552510 | 2021-11-01 06:31:07 | 44501.271608 | 受控输入 |
| 富兰克林·史蒂文斯 | 551625 | 2021-11-01 06:31:40 | 44501.271990 | 大堂 IN |
| 特伦斯·科文顿 | 625825 | 2021-11-01 06:32:25 | 44501.272511 | 受控输入 |
| 麦当劳叔叔 | 201202 | 2021-11-01 06:33:55 | 44501.273553 | 受控输入 |
| 和声之歌 | 111555 | 2021-11-01 06:36:10 | 44501.275115 | 受控输入 |
| 谢丽尔·盖茨 | 111234 | 2021-11-01 06:36:28 | 44501.275324 | 受控输入 |
| 唐娜·马修斯 | 571050 | 2021-11-01 06:36:49 | 44501.275567 | 受控输入 |
答
以下是基于下面接受的答案的示例结果表（此处发布用于格式化），其中的列指示与答案公式匹配。
| G | H | 我 | K |
|---|---|---|---|
| 查找日期 | 查找时间 | 和声之歌 | 约翰·史密斯 |
| 2021-11-01 | 06:28 | 不存在 | 不存在 |
| 2021-11-01 | 06:29 | 44501.269965 | 不存在 |
| 2021-11-01 | 06:30 | 44501.269965 | 不存在 |
| 2021-11-01 | 06:31 | 44501.269965 | 不存在 |
| 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 |
答：
1赞
Mayukh Bhattacharya
8/24/2023
#1
请尝试以下解决方案： 屏幕截图如下所示Example One
• 细胞中使用的配方G4
=INDEX(D:E,MATCH(2,1/((G2=A:A)*((H2+I2)>=C:C)),1),COLUMN(A1))
请注意，由于它是一个数组公式，因此在退出编辑模式时点击CTRL+SHIFT+ENTER
如斯科特·克兰纳爵士的评论中所述进行了更新。不要使用更新的那个拖过来。{1,2}
屏幕截图如下所示Example TWO
由于您需要排除 ，因此请使用以下公式：OfficeLobby INLobby OUT
=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))
评论
1赞
Matthew Sechrist
8/25/2023
这行得通！我确实根据结果表的排列进行了一些更改，我也会发布这些更改。以下是基于上述答案的公式的最终版本：=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")
0赞
Mayukh Bhattacharya
8/25/2023
@MatthewSechrist听起来很棒，很高兴它对你有用=）
1赞
Matthew Sechrist
8/25/2023
|摹 |H |我 |K | |：----------：|：-----：|：------------：|：------------：| |日期 |时间 |和声之歌 |约翰·史密斯 | |2021-11-01 |06：28 |不存在 |不存在 | |2021-11-01 |06：29 |44501.269965 |不存在 | |2021-11-01 |06：30 |44501.269965 |不存在 | |2021-11-01 |06：31 |44501.269965 |不存在 | |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 |
评论
Harmony Song on 2021-11-01 at 6:45=INDEX(D:D,MATCH(2,1/((G2=A:A)*((H2+I2)>=C:C)),1))