JuicyMusic
Board Regular
- Joined
- Jun 13, 2020
- Messages
- 210
- Office Version
- 365
- Platform
- Windows
Hello, I hope someone can help me. I hope my title is okay. I wasn't sure how to write it.
The INDEX / XMATCH formula in column A is working perfectly all the way down the spreadsheet, no matter how many rows each job section has.?. FYI There might bee 50 jobs on this sheet. No issue with this formula.
Excel Formula: =INDEX(C$2:C2,XMATCH("Job#*",C$2:C2,2,-1))
The MAX / ISNUMBER(SEARCH) formula in column B doesn't return the correct results within each job section, on each row. Bold lines show break between different job sections in the spreadsheet so you can see where the job number change happens.
Excel Formula: =IF(MAX(--ISNUMBER(SEARCH(ListRng,D2))),D2,B1)
Here is the logic that I need in column C. Starting at row 2. I will try my best to write it so clearly for you.
LOGIC: I need the formula in C2 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 C2 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...….
Column A: The INDEX/MATCH formula is perfect!.
Column B: The MAX/ISNUMBER formula is not exactly what I need for this spreadsheet.
Column C: I manually inputted the results that I should see in column C for more clarity.
I tried to combine these two formulas in column C with no success. Please let me know if there is another way that I should provide a sample of my spreadsheet. Otherwise, it is an image below.
Thank you so much in advance.
Juicy
The INDEX / XMATCH formula in column A is working perfectly all the way down the spreadsheet, no matter how many rows each job section has.?. FYI There might bee 50 jobs on this sheet. No issue with this formula.
Excel Formula: =INDEX(C$2:C2,XMATCH("Job#*",C$2:C2,2,-1))
The MAX / ISNUMBER(SEARCH) formula in column B doesn't return the correct results within each job section, on each row. Bold lines show break between different job sections in the spreadsheet so you can see where the job number change happens.
Excel Formula: =IF(MAX(--ISNUMBER(SEARCH(ListRng,D2))),D2,B1)
Here is the logic that I need in column C. Starting at row 2. I will try my best to write it so clearly for you.
LOGIC: I need the formula in C2 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 C2 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...….
Column A: The INDEX/MATCH formula is perfect!.
Column B: The MAX/ISNUMBER formula is not exactly what I need for this spreadsheet.
Column C: I manually inputted the results that I should see in column C for more clarity.
I tried to combine these two formulas in column C with no success. Please let me know if there is another way that I should provide a sample of my spreadsheet. Otherwise, it is an image below.
Thank you so much in advance.
Juicy
