Multiple Search

[i82]

Hi All,
How can i add multiple search to sumproduct?
I have tried the below formula and got an error

Thanks

=IF(SUMPRODUCT(--(NOT(ISERR(SEARCH($A$1:$A$3,C1)))))>0,"1",IF(SUMPRODUCT(--(NOT(ISERR(SEARCH($b$1:$b$3,C1)))))>0,"2","")

[JohnTopley]

Perhaps...


=IF(SUMPRODUCT(--(NOT(ISERR(SEARCH($A$1:$A$3,C1)))))>0,"1",IF(SUMPRODUCT(--(NOT(ISERR(SEARCH($B$1:$B$3,C1)))))>0,"2",""))


works OK

with 1,2,3 in A / 4.5.6 in B, then if C1=2, result is 1:if C1=6, result is 2

If you want to use the results to do any calculations, remove quotes ..

=IF(SUMPRODUCT(--(NOT(ISERR(SEARCH($A$1:$A$3,C1)))))>0,1,IF(SUMPRODUCT(--(NOT(ISERR(SEARCH($B$1:$B$3,C1)))))>0,2,""))

[Phuocam]

or change NOT(ISERR()) to ISNUMBER():

=IF(SUMPRODUCT(--ISNUMBER(SEARCH($A$1:$A$3,C1)))>0,"1",IF(SUMPRODUCT(--ISNUMBER(SEARCH($B$1:$B$3,C1)))>0,"2",""))

[i82]

Thanks. Solved