NEED FORMULA TO LOOK TO THE RIGHT AND THEN 1 ROW ABOVE

[JuicyMusic]

Hello experts, I hope that what I'm writing will be clear. Let me know if you need more information.
Here is the formula that I started writing.

=IF(AND(ISNUMBER(SEARCH("*JOB#*",A17)),ISNUMBER(SEARCH("*JOB#*",B17))),B17,
A17)

HERE IS WHAT I NEED THE FORMULA TO DO:
FORMULA LOGIC: If the text string in column B contains the word JOB#, then return the whole string to the same row in column A, if not then return the whole text string from 1 row above (as long as the text string includes the text JOB#).

The formula I need should been in column A. Below is 4 jobs but this whole spreadsheet will have about 50 jobs. Each job has the same number of rows for the type of data. Thank you so much!

[ATTACH type="full"]29197[/ATTACH]

 

[JuicyMusic]

Hello, I see now that I wasn't clear at all I'll write it in the logic below.

FORMULA LOGIC: If the text string in column F contains any of these text strings from this list , then return the whole string to the same row in column E, if not then return the whole text string from 1 row above (as long as the text string contains any of these text strings from this list).

The list will be on a tab named "PR TYPE DATABASE". It can be in any column. It just happens to be in column G & H on my tab. And more could be added to this list in the future. Thank you!!

 

[Fluff]

Give your list a defined name (I've called it ListRng & use

=IF(MAX(--ISNUMBER(SEARCH(ListRng,B2))),B2,A1)

 

[JuicyMusic]

For some reason I had to attached the image of my spreadsheet here instead of in the box above.

 

[JuicyMusic]

I was editing what I wrote but I see that you responded before I could attached my spreadsheet. Let me try what you suggested right now.

 

[JuicyMusic]

Fluff, could you please tell me how to use the ListRng formula? Where do I put it. Thank you so much.

 

[Fluff]

Have a look here to see how to create defined names

Excel names and named ranges: how to define and use in formulas

The tutorial explains what Excel name is and how to define a name for a cell, range, constant or formula. You will also learn how to edit, filter and delete defined names in Excel.

www.ablebits.com

 

[JuicyMusic]

Thank you for the link. I'm looking at it right now. I'll be back if I have any questions.

 

[Fluff]

Ok,

 

[JuicyMusic]

Fluff, I'm back. I did name the range even before I went to the link you left me.
Should I insert the MAX formula in the middle of the first formula? The data list is another a different tab. I attached my spreadsheet and looking to have a formula in column B to return back any text in column D that is in the ListRnge.

Is it at all possible to have the first formula you provided me to look at a range instead of just one wildcard? Thank you

 

[JuicyMusic]

Please forgive me for writing again. I did it! I'm checking the results right now.