Formula to check if value is in array not working

[Jyggalag]

Hi all,

I have this formula I found online:

I have now set the formula to test if my value "test" in cell B2 is located anywhere at all in within the value set B1:D133 in sheet 2.

I literally have the value "test" in cell B2, but it does not work:

It should return the value "1" if the value is in sheet 2.

Any idea why my formula is not working? Would greatly appreciate some help here!

Thank you all

Best regards,
Jyggalag

 

[RoryA]

Match only works on a single column or row. You could use COUNTIF though:

=IF(COUNTIF(Sheet2!B1:D133,B2)>0,1,0)

or just:

=(COUNTIF(Sheet2!B1:D133,B2)>0)+0

 

[Jyggalag]

Match only works on a single column or row. You could use COUNTIF though:

=IF(COUNTIF(Sheet2!B1:D133,B2)>0,1,0)

or just:

=(COUNTIF(Sheet2!B1:D133,B2)>0)+0

Switched to CountIf,

Thank you so much Rory!