I am trying to create a formula to generate the values in the "Found?" column for the table below.
The value should be "Y" if a given value in the Value column can be found in the column whose header cell corresponds to the value in the Category column (otherwise "N").
I can get it to work with this formula, but ideally, it would be an array formula in C1:
=if($A2="","",iferror(if(MATCH($B2,index($D$1:$F,,MATCH($A2,$D$1:$F$1,0)),0),"Y"),"N"))
My attempt at an array formula in C1 doesn't work:
={"Found?"; ARRAYFORMULA(if($A2:$A="","",iferror(if(MATCH($B2:$B,index($D$1:$F,,MATCH($A2:$A,$D$1:$F$1,0)),0),"Y"),"N")))}
| Category | Value | Found? | Colors | Sizes | Flavors |
|---|---|---|---|---|---|
| Colors | Black | Y | Blue | Small | Strawberry |
| Colors | Blue | Y | Red | Medium | Chocolate |
| Colors | Green | Y | Orange | Large | Vanilla |
| Colors | Orange | Y | Yellow | Extra Large | Peach |
| Colors | Pink | N | Green | Orange | |
| Colors | Red | Y | White | ||
| Colors | Violet | N | Black | ||
| Colors | White | Y | |||
| Colors | Yellow | Y | |||
| Sizes | Extra Large | Y | |||
| Sizes | Large | Y | |||
| Sizes | Medium | Y | |||
| Sizes | Small | Y | |||
| Flavors | Blueberry | N | |||
| Flavors | Caramel | N | |||
| Flavors | Chocolate | Y | |||
| Flavors | Coconut | N | |||
| Flavors | Lime | N | |||
| Flavors | Orange | Y | |||
| Flavors | Peach | Y | |||
| Flavors | Pecan | N | |||
| Flavors | Pineapple | N | |||
| Flavors | Strawberry | Y | |||
| Flavors | Vanilla | Y | |||
| Flavors | Watermelon | N |
CodePudding user response：
try this formula in cell C2:
=MAP(A2:A,B2:B,LAMBDA(ax,bx,IF(ax="",,IF(REGEXMATCH(bx,TEXTJOIN("|",1,FILTER(D2:F,D1:F1=ax))),"Y","N"))))
-
ALTERNATE FORMULA
=ARRAYFORMULA(IF(LEN(A2:A),IF(ISERROR(VLOOKUP(A2:A&B2:B,{INDEX(QUERY({IF(LEN(D2:D),D1&D2:D,);IF(LEN(E2:E),E1&E2:E,);IF(LEN(F2:F),F1&F2:F,)},"Select * Where Col1!=''"))},1,)),"N","Y"),))
-
