# Use the SEARCH Function and ignore any characters before what is to be searched

#### Cabe27

##### New Member
Hello all,

Let me describe my sheet:
In column A there are part numbers, in column B there are locations. If you type a part number in cell D5 an array function in column E will search for all matches of that part number and give you all the locations.

Now the problem:
The SEARCH function gives you a TRUE even if the text you are matching is in the middle of the string. So if I type in "12 UNF" into cell D5 I get all the part numbers locations for that in column E, great, BUT! I am also getting the parts "10-12 UNF" because that is how the SEARCH functions searches. I don't want the 10-12 just start searching from 12 UNF and beyond.

Current Equation:
=IF(ISERROR(INDEX(A:B,SMALL(IF(ISNUMBER(SEARCH(\$D\$5,A:B,1)),ROW(A:A)),ROW(2:2)),2)),"",INDEX(A:B,SMALL(IF(ISNUMBER(SEARCH(\$D\$5,A:A,1)),ROW(A:A)),ROW(2:2)),2))

I have had no luck with getting the match function (MATCH(\$D\$5&"*",A:A,0)) to give me a proper TRUE/FALSE array to use my SMALL function with. Perhaps this is the wrong path.

Any help is welcome and I hope someone can give me some guidance.

Thank You,
CABE

### Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

#### pgc01

##### MrExcel MVP
Hi Cabe
Welcome to the board

Compare the input with the left part of the string. Ex., in E3:

=IFERROR(INDEX(B:B,SMALL(IF(LEFT(\$A\$2:\$A\$1000,LEN(\$D\$2))=\$D\$2,ROW(\$A\$2:\$A\$1000)),ROWS(\$E\$2:E2))),"")

Copy down

Remark: it's not efficient to look in the whole column, I've used rows 2:1000 but you can amend it.

#### Cabe27

##### New Member
Eureka! Thank you, your formula is much more efficient as well.

Thank you!

#### pgc01

##### MrExcel MVP
You're welcome. Thanks for the feedback.

Replies
9
Views
509
Replies
7
Views
244
Replies
3
Views
223
Replies
1
Views
69
Replies
3
Views
178

1,127,348
Messages
5,624,145
Members
416,014
Latest member
MickP69

### 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.

### Which adblocker are you using?

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

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