Find Text within Multiple Columns

[Catniption]

Hello my favorite smart people! I need your help. I am trying to look up a value in one column but need to look at 3 others to determine if the value should be there or left blank. Here is what I need:

If B2 contains "P01" then enter qty from A2. If B2 = "none" then look at C2. If C2 contains "P01" then enter qty from A2. If both B2 and C2 are "none" then look at D2. If D2 contains "01" then enter qty from A2. If all columns fail, then leave blank.

I have the following formula, which kind of works, but I don't know how to make it stop looking if the first (or second) cell contains the correct data.

IF(ISNUMBER(SEARCH("P01",$B2)),$A2,IF(ISNUMBER(SEARCH("P01",$C2)),$A2,IF(ISNUMBER(SEARCH("-01",$D2)),$A2,"")))

Column B will usually have the data, otherwise, Column C will. Both of these column's data will be the same format. Column D will always have data but in a different format. It is the last resort. All three columns are also formulas using a lookup.

I attached a small file as an example.
Sample for formula help.xlsx

[Olly]

In E3, enter:

Please Login or Register  to view this content.

Note: EDITED, after I read the column D requirement properly!

[Catniption]

Fabulous! Now, let's throw one more monkey wrench into the mix. We have now extended our search to more columns. What would I need to only have A2's qty in only one cell? See the modified attachment.

Sorry, wrong file. Let's try again:
Sample for formula help1.xlsx

[Olly]

"Invalid attachment specified" when I click your link. Not sure what you mean....

[Catniption]

Crap! I'll try again. It seems to download fine when I click it from here.
Sample for formula help1.xlsx

IF that doesn't work:
This spreadsheet is huge and has 20 columns all looking at the same 3 columns to find what each ones needs. Most are looking for P0x, but some will look for S-TEXT. For those that have S-TEXT (in one of the 3 data columns)that is all I need to find. If data column B says S-TEXT, C says none and D says Planning-01, I only need the column looking for S-TEXT to populate. I do not need the column looking for "01" (as the last option) to populate at all.