ISNUMBER SEARCH Help?

[BrettOlbrys1]

- Column A: list of 1,000 employee IDs
- Column B: list of job descriptions

I want to only return the list of employee IDs (from column A) that have a partial match in column B.

The matching words would be "North", "South", "West", "Central"

Using the sample data below, E564, E1, and E88 would be excluded, and the resulting list will be everyone else.

What formula is needed to accomplish this?

Sample Data:

Column A	Column B
E967	Northeast Sales
E23	Southwest Sales
E564	Project Manager
E888	West Sales
E234	West Sales
E1	President
E88	Project Manager
E478	Central Tech
E99	Southeast PM

 

[Joe4]

Since you are using Excel 365, you can use the new "Filter" function to do this.

Here is your example, with the results in blue:

You would simply put this formula in cell D2, and it populates the rest of the columns/rows in columns D and E:

=FILTER(A2:B10,ISNUMBER(SEARCH("North",B2:B10))+ISNUMBER(SEARCH("South",B2:B10))+ISNUMBER(SEARCH("West",B2:B10))+ISNUMBER(SEARCH("Central",B2:B10)),"")

 

[Cubist]

One more option.

Book1
	A	B	C	D	E
1	ID	Region
2	E967	Northeast Sales		E967	Northeast Sales
3	E23	Southwest Sales		E23	Southwest Sales
4	E564	Project Manager		E888	West Sales
5	E888	West Sales		E234	West Sales
6	E234	West Sales		E478	Central Tech
7	E1	President		E99	Southeast PM
8	E88	Project Manager
9	E478	Central Tech
10	E99	Southeast PM
Sheet2

Cell Formulas
Range	Formula
D2:D7	D2	=UNIQUE(TOCOL(IFS(ISNUMBER(SEARCH({"North","South","Central","West"},B2:B10)),A2:A10),2))
E2:E7	E2	=XLOOKUP(D2#,A2:A10,B2:B10)
Dynamic array formulas.

 

[Fluff]

Another option

=FILTER(A2:B100,BYROW(ISNUMBER(SEARCH({"north","south","west","central"},B2:B100)),LAMBDA(br,SUM(--br))))

 

[Georgiboy]

And one more:

Book1
	A	B	C	D	E
1	Column A	Column B
2	E967	Northeast Sales		E967	Northeast Sales
3	E23	Southwest Sales		E23	Southwest Sales
4	E564	Project Manager		E888	West Sales
5	E888	West Sales		E234	West Sales
6	E234	West Sales		E478	Central Tech
7	E1	President		E99	Southeast PM
8	E88	Project Manager
9	E478	Central Tech
10	E99	Southeast PM
Sheet1

Cell Formulas
Range	Formula
D2:E7	D2	=FILTER(A2:B10,BYROW(IFERROR(SEARCH({"North","South","West","Central"},B2:B10),0),LAMBDA(x,SUM(x))))
Dynamic array formulas.

 

[Rick Rothstein]

And yet one more (somewhat short) formula...

=FILTER(A2:B10,LEN(TEXTAFTER(B2:B10&"x",{"north","east","south","west","central"},,1,,"")))