Problem with multiple if and search formula in excel

0
votes

I've created multiple ifs and searches formula in exel but only if first condition is true formula works, if first condition is false formula returns ARG#. 

=IF(SEARCH($L$5;$A15);N$5;IF(SEARCH($L$6;$A15);N$6;IF(SEARCH($L$7;$A15);N$7;IF(SEARCH($L$8;$A15);N$8))))

Also if I separate the formula, ich IF tree works fine alone. How can I fix that?

The idea of formula is to check if cells in A column contains text from range L5:L8, and if contains eg. L6, the value will be N7.

enter image description here

1
excelexcel-formula
please share some sample data in column A, L and N and indicate the expected output with the logic behind. – Terry W

1 Answers

0
votes

Hm, it should return #VALUE, not ARG# if not found by the first SEARCH().

Anyway, ISNUMBER() converts that to a Boolean value:

=IF(ISNUMBER(SEARCH($L$5; $A15)); N$5;
 IF(ISNUMBER(SEARCH($L$6; $A15)); N$6;
 IF(ISNUMBER(SEARCH($L$7; $A15)); N$7;
 IF(ISNUMBER(SEARCH($L$8; $A15)); N$8))))