I have an ifs statement that is checking for 1 out of X keywords. Depending on which keyword is in there, it returns any value. Boiled down its something like this:
ifs(
Cell1="Text1",0,
Cell1="Text2",0.5,
Cell1="Text3",1
)
So currently I either get 0, 0.5 or 1 as an output. Per if statement one value gets returned.
I am looking for a way to just have one if statement checking the Cell and return a value based on what we find. To explain the idea with wrong syntax:
if(
Cell1="Text1"or"Text2"or"Text3"
return:
0 if its "Tex1" or
0.5 if its "Text2" or
1 if its "Text2"
)
I am struggling with the syntax and logic behind it. Here is the actual formula I am currently using, I hope that helps. Without context this might seem overly complicated. My main point is to have these three statements combined into one to shorten the formula as much as possible.
ifs(
VLOOKUP($A2, Sheet1!$A$3:$W, index(filter(arrayformula(column(Sheet1!$A$1:$1)),Sheet1!$A$1:$1=E$1),,1),false)="text1",0,
VLOOKUP($A2, Sheet1!$A$3:$W, index(filter(arrayformula(column(Sheet1!$A$1:$1)),Sheet1!$A$1:$1=E$1),,1),false)="text2",0.5,
VLOOKUP($A2, Sheet1!$A$3:$W, index(filter(arrayformula(column(Sheet1!$A$1:$1)),Sheet1!$A$1:$1=E$1),,1),false)="text3",1)
Aucun commentaire:
Enregistrer un commentaire