如何索引匹配的单元格数组以查看每个单元格是否匹配?

问题描述

我想要一个索引匹配(不必是这个公式)来遍历这些字符串的数组:

I want an index match (doesn't have to be this formula) to run through an array of these string:

black
white
yellow
red
green
blue
light blue

查看它们是否会与以下任何一个单元格部分匹配:

to see if they would partial match with any of these cells:

black t shirt
white t shirt
yellow t shirt
red t shirt
green t shirt

我该怎么做?

我尝试过:

=INDEX(!colors(MATCH(!"*"&{color1,color2,color3,color4}&"*",!tshirts)

我希望输出为t恤旁边一栏中的颜色以表示匹配

I want the output to be the color in a column beside the t shirt to indicate a match

black - black t shirt
white - white t shirt
red - red t shirt
etc

这似乎可行，但简直太烂了.我正在寻找更干净的代码:

This seems to work, but it's pretty darn janky. I'm looking for cleaner code:

这里变化的变量是我所指的颜色

The changing variable here is the color i'm referencing

=IFERROR(VLOOKUP("*"&G2&"*",I3,1,FALSE),IFERROR(VLOOKUP("*"&G3&"*",I3,1,FALSE),IFERROR(VLOOKUP("*"&G4&"*",I3,1,FALSE),IFERROR(VLOOKUP("*"&G5&"*",I3,1,FALSE),IFERROR(VLOOKUP("*"&G6&"*",I3,1,FALSE),IFERROR(VLOOKUP("*"&G7&"*",I3,1,FALSE),IFERROR(VLOOKUP("*"&G8&"*",I3,1,FALSE),"error")))))))

推荐答案

尝试:

=ARRAYFORMULA(IFNA(VLOOKUP(""&ROW(C1:C), SUBSTITUTE(REGEXREPLACE(TRIM(
 SPLIT(FLATTEN(QUERY(QUERY(SPLIT(FLATTEN(ROW(C1:C)&"♠♣"&IFERROR(TRIM(
 SPLIT(LOWER($C1:$C), " t ", 0, 0))&"♦")), "♣"),
 "select max(Col2)
  where Col2 matches '^"&TEXTJOIN("$|^", 1, IF(A1:A="",,LOWER(A1:A)&"♦"))&"$'
  group by Col2 pivot Col1"),,9^9)), "♠")), "♦$", ), "♦", ","), 2, 0)))

或更短(仅适用于一种颜色):

or shorter (works only with one color):

=INDEX(IFNA(REGEXEXTRACT(C1:C, TEXTJOIN("|", 1, SORT(A1:A, 1, 0)))))

这篇关于如何索引匹配的单元格数组以查看每个单元格是否匹配?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！