IF and Search function issue

[Bram84]

Ok. I need your help guys , and girls.

=IF(A4>1;(IF(SEARCH("BP";INPUT!BF1;1);"PALLET"); "SHELF");"")

This one won't work. I actually want the following...
The search for BS is not yet in the formula.

So I actually need
- If you search for BP in the cell INPUT!BF1, the result should be PALLET
- If you search for BS in the cell INPUT!BF1, the result should be SHELF
- If the cell INPUT!BF1 is empty, the result should be a blank cell.

Thanks all.

[JohnTopley]

Try

=IF(A4>1,IF(Input!BF1="","",IF(ISNUMBER(SEARCH("BP",Input!BF1,1)),"PALLET","SHELF")),"")

Assumption is that BF1 contains either BP or BS

Change "," to ";"

[gmr4evr1]

If you are always looking in the cell Input BF1, you could use this...

Please Login or Register  to view this content.

[ChemistB]

IF BP or BS might be inside a larger string

=IF(INPUT!BF1<>""; IF(ISNUMBER(SEARCH("BP";INPUT!BF1));"PALLET"; IF(ISNUMBER(SEARCH("BS";INPUT!BF1)); "SHELF"; "N/A"));"")

The "N/A" appears if BF1 has a value but it doesn't contain BP or BS

[Bram84]

Thx,
But in cells where there is BP or BS, he still returns a blanco cell
I used =IF(A4<1;IF(INPUT!BF1="";"";IF(ISNUMBER(SEARCH("BP";INPUT!BF1;1));"PALLET";"SHELF"));"")

[JohnTopley]

Check .....

=IF(A4<1;IF(INPUT!BF1="";"";IF(ISNUMBER(SEARCH("BP";INPUT!BF1;1));"PALLET";"SHELF"));"")

Your post was A4>1

[Bram84]

This one works almost...
Only he there should be a check if Cell A4 had a number bigger than 1 BEFORE he starts looking. So of the cell A4 is Blanco, the result must be blanco as wel.
Is the call A4 bigger than 1, than he should search for BS or BP

Thx!!

IF BP or BS might be inside a larger string

=IF(INPUT!BF1<>""; IF(ISNUMBER(SEARCH("BP";INPUT!BF1));"PALLET"; IF(ISNUMBER(SEARCH("BS";INPUT!BF1)); "SHELF"; "N/A"));"")

The "N/A" appears if BF1 has a value but it doesn't contain BP or BS

[JohnTopley]

TRY

=IF(OR(A4="",Input!BF1=""),"",IF(A4>1,IF(ISNUMBER(SEARCH("BP",Input!BF1,1)),"PALLET","SHELF"),""))

blank if either A4 or BF1 are blank OR A4 <=1

[Bram84]

Works!!
You Rock :-)
Thx!!