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]

This worked perfectly except for one thing. It doesn't give back a correct result from row 2 thru 7. This is because the first instance of a text matches a text that is in the "ListRnge" occurs at row 8 on my spreadsheet.

HERE IS THE FORMULA THAT I PUT IN COLUMN B:

=IF(MAX(--ISNUMBER(SEARCH(ListRng,D2))),D2,B1)

I've attached my spreadsheet so you see how the formula is returning the NAME of the column at the first 7 rows.

 

[Fluff]

Please do not put images inside tags.

It doesn't give back a correct result from row 2 thru 7

Yes it does, because that is what you originally wanted & you didn't say it had changed.
Just delete the formula from B2

 

[JuicyMusic]

Thank you for your patience. I deleted B2 and itt does work. I appreciate you responding back all these time. SOLVED! Again.
Merci,
Juicy

 

[Fluff]

Glad to help & thanks for the feedback.

 

[JuicyMusic]

Hello Fluff, I am back again this morning because I just noticed something that is not working exactly as I need. Both formula's are so nice and I will be using them on different spreadsheets for sure.

The MAX formula that you provided me yesterday is doing almost everything I need. It looks like it needs to be tweaked just little bit.

Let me explain?

The INDEX / XMATCH formula is in column A and it is working perfectly all the way down the spreadsheet, no matter how many rows each job section has.?

=INDEX(C$2:C2,XMATCH("Job#*",C$2:C2,2,-1))

The MAX / ISNUMBER(SEARCH) formula is in column B and seems to be bringing back the correct result sometimes:

=IF(MAX(--ISNUMBER(SEARCH(ListRng,D2))),D2,B1)

Here is the logic that I need in column B. Starting at row 2. I will try my best to write it so clearly for you.

LOGIC: I need the formula in B2 to look in column E - and look for the 1st occurrence of a text - that matches any of the texts - listed in the range named "ListRng".
If it finds a matching text - (such as "Non Union") in column E, then return that text (ex: Non Union) to B2 and to the rows below until it encounters a job number change in column A.

When it encounters a job number change in column A, then look in column E again - and look for the next occurrence of a text - that matches any of the texts - listed in the range named "ListRng". And so on...….

If you don't mind, I'm attaching the spreadsheet again for you.
Column A: The INDEX/MATCH formula is perfect!.
Column B: The MAX/ISNUMBER formula almost perfect.
Column C: I manually inputted the results that I should see in column C for more clarity.

I am so excited to see what you come back with. I tried to combine these two formulas together. Sometimes it almost worked above but not below. But I'm still trying while I wait for your response.
I can't wait!!!? Thank you. I hope you're in today?

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

 

[Fluff]

This is now a totally different question, so you will need to start a new thread. Thanks

Also please do not put your images inside tags.

 

[JuicyMusic]

Hi Fluff, I'll start a new thread.
I copied and pasted my spreadsheet inside the tags. I thought this was the way to do it. Thanks! Have a nice weekend!