使用命名范围是一个很好的步骤;动态命名范围甚至更好。然而,它们通过尝试将正确的相对行号返回给索引功能 https://support.office.com/en-us/article/index-function-0ee99cef-a811-4762-8cfb-a222dd31368a当它们不在第一行开始时。
我使用了两个命名范围(例如 HARDWARE 和 HARDWARE_PART)指:公式为,
=Sheet1!$AA$2:INDEX(Sheet1!$AA:$AA, MATCH("zzz",Sheet1!$AA:$AA))
=Sheet1!$AB$2:INDEX(Sheet1!$AB:$AB, MATCH("zzz",Sheet1!$AA:$AA))
这需要修改返回的行1因为他们从第二排开始。
B1中的标准公式是:
=IFERROR(INDEX(HARDWARE_PART, AGGREGATE(15, 6, (ROW(HARDWARE)-1)/ISNUMBER(SEARCH(HARDWARE, $A1)), COLUMN(A:A))), "")
按优先级降序填充后续匹配项的右侧。
An 错误函数 https://support.office.com/en-us/article/IFERROR-function-F59BACDC-78BD-4924-91DF-A869D0B08CD5“wrapper”可以避免显示#NUM!
通过用零长度字符串替换错误返回来解决不匹配的错误。
如果您想反转优先级,请交换15 (小功能 https://support.office.com/en-us/article/SMALL-function-4050a82e-659a-4ef6-a970-c963a6f69c29) 骨料1 https://support.office.com/en-us/article/AGGREGATE-function-43B9278E-6AA7-4F17-92B6-E19993FA26DF选项14 (大函数 https://support.office.com/en-in/article/large-function-3688ad91-0b0c-43db-8ef8-825a5e5871db).
xl2010 之前的附录
对于无法使用 AGGREGATE 函数的 Excel 版本,此标准非 CSE 公式会重复结果。在 B1 中,
=IFERROR(INDEX(HARDWARE_PART, SMALL(INDEX(ROW(HARDWARE)-1+ISERROR(SEARCH(HARDWARE, $A1))*1E+99, , ), COLUMN(A:A))), "")
¹ The AGGREGATE function https://support.office.com/en-us/article/AGGREGATE-function-43B9278E-6AA7-4F17-92B6-E19993FA26DF was introduced with Excel 2010. It is not available in earlier versions.