SEARCH COLUMN FOR SPECIFIC TEXT WITH 1 CRITERIA ANOTHER COLUMN

JuicyMusic

Board Regular
Joined
Jun 13, 2020
Messages
94
Office Version
  1. 365
Platform
  1. 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
 

Attachments

  • Dispatch_C.PNG
    Dispatch_C.PNG
    58.6 KB · Views: 17

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

EFANYoutube

Board Regular
Joined
May 19, 2017
Messages
162
Try this in B2 and fill down?

Excel Formula:
=INDIRECT("E"&MATCH(A2,D:D,0)+6)
 
Last edited:

JuicyMusic

Board Regular
Joined
Jun 13, 2020
Messages
94
Office Version
  1. 365
Platform
  1. Windows
Absolutely Perfect! Exactly what I need.
What a simple looking formula - but so powerful.. Thank You!!!!!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,459
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Another option, without using volatile functions.
Excel Formula:
=INDEX("E:E",MATCH(A2,D:D,0)+6)
 

JuicyMusic

Board Regular
Joined
Jun 13, 2020
Messages
94
Office Version
  1. 365
Platform
  1. Windows
Hello Fluff, I'm happy to see you on my thread on this day.

I pasted your formula in C2 and drilled down. I received an #REF! error all the way down.
I'm going to google what "volatile function" is while I wait for your response😊

Merci,
Juicy
 

EFANYoutube

Board Regular
Joined
May 19, 2017
Messages
162

ADVERTISEMENT

Volitile just means it will run evey time calculate workbook is called by excel. not an issue if you have a couple hundred rows but if you put them everywhere they make calculating sluggish.
other function like this are TODAY() and NOW()
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,459
Office Version
  1. 365
Platform
  1. Windows
Solution

JuicyMusic

Board Regular
Joined
Jun 13, 2020
Messages
94
Office Version
  1. 365
Platform
  1. Windows
This tab will have between 3500 to 4500 rows.
 

JuicyMusic

Board Regular
Joined
Jun 13, 2020
Messages
94
Office Version
  1. 365
Platform
  1. Windows
I removed the quotes and the formula worked perfectly. Thank you again!
 

Watch MrExcel Video

Forum statistics

Threads
1,127,405
Messages
5,624,574
Members
416,036
Latest member
eloisa manzanarez

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top