如何从类别列表中返回正确的类别。当它不是单词的完全匹配时？

How do I return a correct category from a list of categories. When it's not an exact match of words?

提问人：MWF 提问时间：7/27/2023 最后编辑：Forward EdMWF 更新时间：7/27/2023 访问量：69

问：

我有一个类别列表，这里是列表，它包含在一个下拉列表中。这是第一列

Product Type
Nails
Screws
Bolts
Nuts
Anchors
Staples
Washers
Sets
Powder Loads
Rivets
Rivet Tool
Hammer Tacker
Staple Gun
Soldering Iron
Insert Tool
Caps

我想浏览此项目类型列表，并将它们对齐/分类为正确的类别。这是列表。这在K列中

Item Type: Fender Washer
Item Type: Fender Washer
Item Type: Cap Screw
Item Type: Cap Screw
Item Type: Sleeve Anchor
Item Type: Hex Lag Screw
Item Type: Carriage Bolt
Item Type: Deck Screw
Item Type: Deck Screw
Item Type: Hex Nut
Item Type: Hex Nut
Item Type: Sleeve Anchor
Item Type: Hex Lag Screw
Item Type: Cap Screw
Item Type: Hex Nut
Item Type: Screw Anchor
Item Type: Hex Nut
Item Type: Hex Nut
Item Type: Carriage Bolt
Item Type: Cap Screw
Item Type: Hex Nut
Item Type: Sleeve Anchor
Item Type: Hex Nut
Item Type: Hex Nut
Item Type: Carriage Bolt
Item Type: Masonry Screw
Item Type: Plow Bolt
Item Type: Hex Lag Screw
Item Type: Cap Screw
Item Type: Carriage Bolt
Item Type: Hex Nut
Item Type: Cap Screw
Item Type: Cap Screw
Item Type: Carriage Bolt
Item Type: Carriage Bolt
Item Type: Carriage Bolt
Item Type: Carriage Bolt
Item Type: Carriage Bolt
Item Type: Screw Anchor
Item Type: Hex Nut
Item Type: Cap Screw
Item Type: Carriage Bolt
Item Type: Hex Nut
Item Type: Carriage Bolt
Item Type: Cap Screw
Item Type: Carriage Bolt
Item Type: Hex Lag Screw
Item Type: Cap Screw
Item Type: Cap Screw
Item Type: Hex Lag Screw
Item Type: Hex Nut
Item Type: Plow Bolt
Item Type: Sleeve Anchor
Item Type: Cap Screw
Item Type: Wall Anchor
Item Type: Hex Nut
Item Type: Hex Nut
Item Type: Carriage Bolt
Item Type: Hex Nut
Item Type: Hex Nut
Item Type: Sleeve Anchor
Item Type: Masonry Screw
Item Type: Cap Screw
Item Type: Cap Screw
Item Type: Carriage Bolt
Item Type: Carriage Bolt
Item Type: Cap Screw
Item Type: Cap Screw
Item Type: Cap Screw
Item Type: Hex Lag Screw
Item Type: Wall Anchor
Item Type: Sheet Metal Screw
Item Type: Carriage Bolt
Item Type: Sheet Metal Screw
Item Type: Cap Screw
Item Type: Carriage Bolt
Item Type: Hex Lag Screw
Item Type: Hex Nut
Item Type: Deck Screw
Item Type: Hex Nut
Item Type: Hex Nut
Item Type: Cap Screw
Item Type: Plow Bolt
Item Type: Hex Nut
Item Type: Screw Anchor
Item Type: Cap Screw
Item Type: Cap Screw
Item Type: Cap Screw
Item Type: Cap Screw
Item Type: Cap Screw
Item Type: Hex Lag Screw
Item Type: Sleeve Anchor
Item Type: Masonry Screw
Item Type: Cap Screw
Item Type: Cap Screw
Item Type: Cap Screw
Item Type: Carriage Bolt
Item Type: Hex Lag Screw
Item Type: Hex Lag Screw
Item Type: Hex Nut
Item Type: Hex Nut
Item Type: Socket Cap Screw
Item Type: Sheet Metal Screw
Item Type: Cap Screw
Item Type: Hex Lag Screw
Item Type: Cap Screw
Item Type: Cap Screw
Item Type: Cap Screw
Item Type: Hex Lag Screw
Item Type: Cap Screw
Item Type: Cap Screw
Item Type: Cap Screw
Item Type: Cap Screw
Item Type: Cap Screw
Item Type: Carriage Bolt
Item Type: Hex Lag Screw
Item Type: Sleeve Anchor
Item Type: Sheet Metal Screw
Item Type: Masonry Screw
Item Type: Cap Screw
Item Type: Cap Screw
Item Type: Tap Bolt
Item Type: Carriage Bolt
Item Type: Carriage Bolt
Item Type: Wall Anchor
Item Type: Carriage Bolt
Item Type: Fender Washer
Item Type: Hex Nut
Item Type: Cap Screw
Item Type: Carriage Bolt
Item Type: Carriage Bolt
Item Type: Hex Nut
Item Type: Cap Screw
Item Type: Cap Screw
Item Type: Cap Screw
Item Type: Cap Screw
Item Type: Cap Screw
Item Type: Cap Screw
Item Type: Screw Anchor
Item Type: Fender Washer
Item Type: Cap Screw
Item Type: Cap Screw
Item Type: Cap Screw
Item Type: Cap Screw
Item Type: Cap Screw
Item Type: Carriage Bolt
Item Type: Hex Nut
Item Type: Sleeve Anchor
Item Type: Screw Anchor
Item Type: Masonry Screw
Item Type: Cap Screw
Item Type: Cap Screw
Item Type: Hex Lag Screw
Item Type: Tap Bolt
Item Type: Screw Anchor
Item Type: Sheet Metal Screw
Item Type: Cap Screw
Item Type: Tap Bolt
Item Type: Carriage Bolt
Item Type: Hex Lag Screw
Item Type: Cap Screw
Item Type: Cap Screw
Item Type: Cap Screw
Item Type: Cap Screw
Item Type: Cap Screw
Item Type: Cap Screw
Item Type: Carriage Bolt
Item Type: Sheet Metal Screw
Item Type: Hex Nut
Item Type: Masonry Screw
Item Type: Cap Screw
Item Type: Cap Screw
Item Type: Cap Screw
Item Type: Cap Screw
Item Type: Cap Screw
Item Type: Cap Screw
Item Type: Cap Screw
Item Type: Cap Screw
Item Type: Tap Bolt
Item Type: Hex Lag Screw
Item Type: Hex Lag Screw
Item Type: Sheet Metal Screw
Item Type: Carriage Bolt
Item Type: Sheet Metal Screw
Item Type: Cap Screw
Item Type: Cap Screw
Item Type: Cap Screw
Item Type: Cap Screw
Item Type: Cap Screw
Item Type: Cap Screw
Item Type: Screw Anchor
Item Type: Carriage Bolt
Item Type: Socket Cap Screw
Item Type: Socket Cap Screw
Item Type: Sheet Metal Screw
Item Type: Sheet Metal Screw
Item Type: Sheet Metal Screw
Item Type: Cap Screw
Item Type: Cap Screw
Item Type: Cap Screw
Item Type: Tap Bolt
Item Type: Tap Bolt
Item Type: Sheet Metal Screw
Item Type: Sheet Metal Screw
Item Type: Cap Screw
Item Type: Cap Screw
Item Type: Cap Screw
Item Type: Cap Screw
Item Type: Tap Bolt
Item Type: Carriage Bolt
Item Type: Socket Cap Screw
Item Type: Cap Screw
Item Type: Cap Screw
Item Type: Cap Screw
Item Type: Cap Screw
Item Type: Cap Screw
Item Type: Cap Screw
Item Type: Cap Screw
Item Type: Cap Screw
Item Type: Tap Bolt
Item Type: Sheet Metal Screw
Item Type: Masonry Screw
Item Type: Hex Lag Screw
Item Type: Screw Anchor
Item Type: Sheet Metal Screw
Item Type: Sheet Metal Screw
Item Type: Cap Screw
Item Type: Cap Screw
Item Type: Cap Screw
Item Type: Deck Screw
Item Type: Deck Screw
Item Type: Deck Screw
Item Type: Sheet Metal Screw
Item Type: Socket Cap Screw
Item Type: Cap Screw
Item Type: Cap Screw
Item Type: Hex Lag Screw
Item Type: Sheet Metal Screw
Item Type: Cap Screw
Item Type: Cap Screw
Item Type: Cap Screw
Item Type: Fender Washer
Item Type: Lock Nut
Item Type: Sheet Metal Screw
Item Type: Sheet Metal Screw
Item Type: Sheet Metal Screw
Item Type: Sheet Metal Screw
Item Type: Cap Screw
Item Type: Cap Screw
Item Type: Cap Screw
Item Type: Cap Screw
Item Type: Hex Lag Screw
Item Type: Lock Nut
Item Type: Cap Screw
Item Type: Cap Screw
Item Type: Tap Bolt
Item Type: Carriage Bolt
Item Type: Sheet Metal Screw
Item Type: Sheet Metal Screw
Item Type: Sheet Metal Screw
Item Type: Lock Nut
Item Type: Self-Drilling Screw
Item Type: Cap Screw
Item Type: Cap Screw
Item Type: Cap Screw
Item Type: Cap Screw
Item Type: Cap Screw
Item Type: Cap Screw
Item Type: Cap Screw
Item Type: Tap Bolt
Item Type: Sheet Metal Screw
Item Type: Cap Screw
Item Type: Cap Screw
Item Type: Hex Lag Screw
Item Type: Sheet Metal Screw
Item Type: Sheet Metal Screw
Item Type: Cap Screw
Item Type: Sheet Metal Screw
Item Type: Cap Screw
Item Type: Cap Screw
Item Type: Carriage Bolt
Item Type: Sheet Metal Screw
Item Type: Sheet Metal Screw
Item Type: Sheet Metal Screw
Item Type: Sheet Metal Screw
Item Type: Sheet Metal Screw
Item Type: Cap Screw
Item Type: Sheet Metal Screw
Item Type: Tap Bolt
Item Type: Hex Lag Screw
Item Type: Sheet Metal Screw
Item Type: Sheet Metal Screw
Item Type: Cap Screw
Item Type: Cap Screw
Item Type: Sheet Metal Screw
Item Type: Sheet Metal Screw
Item Type: Sheet Metal Screw
Item Type: Sheet Metal Screw
Item Type: Cap Screw
Item Type: Cap Screw
Item Type: Sheet Metal Screw
Item Type: Lock Nut
Item Type: Cap Screw
Item Type: Cap Screw
Item Type: Cap Screw
Item Type: Sheet Metal Screw
Item Type: Sheet Metal Screw
Item Type: Sheet Metal Screw
Item Type: Sheet Metal Screw
Item Type: Lock Nut
Item Type: Sheet Metal Screw
Item Type: Self-Drilling Screw
Item Type: Sheet Metal Screw
Item Type: Sheet Metal Screw
Item Type: Sheet Metal Screw
Item Type: Cap Screw
Item Type: Cap Screw
Item Type: Cap Screw
Item Type: Tap Bolt
Item Type: Carriage Bolt
Item Type: Sheet Metal Screw
Item Type: Lock Nut
Item Type: Sheet Metal Screw
Item Type: Sheet Metal Screw
Item Type: Cap Screw
Item Type: Cap Screw
Item Type: Cap Screw
Item Type: Carriage Bolt
Item Type: Carriage Bolt
Item Type: Sheet Metal Screw
Item Type: Sheet Metal Screw

我尝试了各种类型的索引和匹配，left（cell，3），但它仍然找不到正确的字符并返回正确的类别。当它出于某种原因返回类别时，它会返回 Fender Washer 项目类型的 Nails。

Excel 列表 excel-公式 匹配

评论

1赞 JvdV 7/27/2023
请使用正确的 markdown 语法修复您的帖子。这是不可读的
1赞 cybernetic.nomad 7/27/2023
请提供您的数据和预期结果的最小示例
1赞 Chris Maurer 7/27/2023
带帽螺钉是盖子还是螺丝？你有很多没有告诉我们。
1赞 Forward Ed 7/27/2023
@JvdV完成了...虽然很高兴看到预期的输出会是什么
1赞 David Leal 7/27/2023
请提供输入样本的预期输出（或输入数据的子集），以便我们更好地了解要应用的规则？谢谢

答：

0赞 Cantabrigian 7/27/2023 #1

最好提供最少的数据子集，既可以节省空间，又可以准确阐明您的要求，而您似乎已经引用了整个产品类型列表。

产品类型似乎在项目类型中指定，尽管产品类型似乎经常（但不一致）复数，这使得查找更加复杂。撇开这一点不谈，像这样的东西应该有效：

    =LET(
        lstWrd, TEXTAFTER( K2, " ", -1),
        ctgs, I$1:I$23,
        isPlrl, LOWER( RIGHT( TRIM( ctgs), 1)) = "s",
        ctg, IF( isPlrl, LEFT( ctgs, LEN( ctgs) - 1), ctgs)),
        XLOOKUP( lstWrd, ctg, ctg)
    )

这将查找“项目类型”列表中的第二项（K2 - 忽略标题），并从第一列的产品类型列表中返回类别。

它使用 LET 函数中的新 Ecel 文本函数 （TEXTAFTER） 按顺序：

  • 从“项目类型”列表中获取最后一个单词
  • 获取产品类型列表（标记为 ctgs）
  • 检查该列表中的每个项目是否为复数 [有一个最后的 s]
  • 如果有，请删除最后一个字母并标记该单数 CTG 列表
  • 在“产品类型”的单数列表中查找“项目类型”的最后一个单词

评论

0赞 Cantabrigian 7/27/2023
虽然我支持上述内容，并希望它包含您会发现有用的技术，但所述问题是不寻常的，可能会导致错误：您通常不能依赖产品类型本质上是项目类型的子字符串。当某人（其他人）添加了不符合此约定的新项目类型时，这种情况可能随时更改。更常见（且稳健的是），您的 Excel 工作表中会有一个数组 elsewehere，该数组为每个项目类型分配一个产品类型。然后，您将查找每个项目的产品类型。
0赞 MWF 7/28/2023
谢谢你的帮助。我也会澄清我的问题。

上一个：如果多个匹配项，MATCH 将产生相同的结果

下一个：Excel：将部分文本搜索索引匹配到另一个工作表中