Look for multiple values in a range and decide by if function what the result is, if these values are found.

[Bossie]

Hi there,

What I'm trying to achieve is to show some text in column W, if the previous columns in the same row contain 1 or more certain values.
If i try this formula for 1 value, there is no problem, but when I look for multiple values I fail.

I'm guessing that using an array formula could be a solution? If so, I keep struggling to make this work..

 

[jasonb75]

What values do you want to look for, where do you want to look for them, and what should the results be for each value found?

From your screen capture and attempted formula, my best guess would be the following, which you will need to array confirm with Ctrl Shift Enter.

=IF(OR(N2:V2={125\128\138});"Company1";"Company2")

 

[Fluff]

Maybe
=IF(SUM(COUNTIFS(N2:V2,{125,128,138})),"Company1","Company2")

 

[Bossie]

What values do you want to look for, where do you want to look for them, and what should the results be for each value found?

From your screen capture and attempted formula, my best guess would be the following, which you will need to array confirm with Ctrl Shift Enter.

=IF(OR(N2:V2={125\128\138});"Company1";"Company2")

Maybe
=IF(SUM(COUNTIFS(N2:V2,{125,128,138})),"Company1","Company2")

Both work perfect as a solution for the question I raised, thanks guys!

 

[Fluff]

Glad we could help & thanks for the feedback.