如何实现lookup_array与return_array可灵活配置的XLOOKUP查询？
2026-6-21
灵活配置Lookup和Return数组的Excel公式需求
| 0 | A | B | C | D | 2023-S | 2023-M | G | 2024-S | 2024-M | J | selected data | L | M |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Products | Shop | |||||||||||
| 2 | lookup_array | ||||||||||||
| 3 | Product A | Shop3 | 80 | 2% | 120 | 22% | 2024-S | ||||||
| 4 | Product B | Shop1 | 320 | 17% | 400 | 15% | return_array | ||||||
| 5 | Product B | Shop3 | 90 | 30% | 750 | 8% | selected data | 2024-M | |||||
| 6 | Product B | Shop2 | 500 | 4% | 70 | 4% | 400 | 15% | |||||
| 7 | Product C | Shop2 | 160 | 10% | 245 | 10% | 400 | 35% | |||||
| 8 | Product D | Shop1 | 500 | 8% | 130 | 4% | 70 | 4% | |||||
| 9 | Product D | Shop4 | 130 | 11% | 130 | 4% | 520 | 42% | |||||
| 10 | Product E | Shop2 | 75 | 8% | 650 | 15% | 130 | 4% | |||||
| 11 | Product E | Shop1 | 60 | 47% | 90 | 7% | 90 | 7% | |||||
| 12 | Product E | Shop4 | 500 | 25% | 400 | 35% | 130 | 4% | |||||
| 13 | Product E | Shop3 | 350 | 9% | 140 | 13% | 130 | 9% | |||||
| 14 | Product F | Shop2 | 60 | 30% | 130 | 9% | 70 | 16% | |||||
| 15 | Product G | Shop2 | 90 | 5% | 370 | 12% | |||||||
| 16 | Product H | Shop1 | 390 | 27% | 70 | 16% | |||||||
| 17 | Product H | Shop2 | 70 | 18% | 520 | 42% |
需求说明
需要在单元格区域M6:M14中，根据K6:K14的值返回对应数据，同时满足以下灵活配置需求：
lookup_array由M3单元格的输入值决定；return_array由M5单元格的输入值决定（例如将M5从2024-M切换为Product时，应返回A列对应的产品名称）。
现有公式框架
已构思出如下初步公式框架：
=LET( _A, K6:K14, _B, >> flexible based on input in Cell M3 <<, _RollingA, MAP(_A,LAMBDA(α,COUNTIF(α:K6,α))), _RollingB, MAP(_B,LAMBDA(α,COUNTIF(α:H3,α))), XLOOKUP(_A&"|"&_RollingA,_B&"|"&_RollingB,XLOOKUP(M5,A1:I1,A3:I17)))
关键注意事项
注意：lookup_array中的值不唯一（如示例中的130、400和70）。因此，当K6:K14中值的出现频次与lookup_array中不一致时，M6:M14中的数据需按降序匹配到K6:K14的值。（上述示例公式已按此逻辑处理）
问题
需编写何种公式，才能实现M6:M14区域基于可灵活配置的lookup_array和return_array的查询需求？
内容的提问来源于stack exchange，提问作者Michi
相关产品推荐
相关产品推荐
