# Help Seaching array

#### Birtles

##### New Member
Hi Guys,

Please could you help me, i have 2 lists and want to search list 2 to see if there are any partial matches to list 1.

eg.

List 1
ball
light
hoop

List 2
cloud
apple
soccerball
road
trafficlight

ive tried {=SEARCH(\$A\$1:\$A\$3,B1:B5,1)} as the logic seems correct but i am obviously not on the right track and get 3 #VALUE! and 2 #N/A results

Could you please assist.

Many Thanks!
Clinton

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
You can use vlookup instead of search

=IFERROR(VLOOKUP(A2,\$B:\$B,1,0),"")

paste in col C2 and drag it till the list1

Hi Guys,

Please could you help me, i have 2 lists and want to search list 2 to see if there are any partial matches to list 1.

eg.

List 1
ball
light
hoop

List 2
cloud
apple
soccerball
road
trafficlight

ive tried {=SEARCH(\$A\$1:\$A\$3,B1:B5,1)} as the logic seems correct but i am obviously not on the right track and get 3 #VALUE! and 2 #N/A results

Could you please assist.

Many Thanks!
Clinton

Do you want a result per record in B or a global diagnostic result i.e., a single value og 1 or 0?

Do you want a result per record in B or a global diagnostic result i.e., a single value og 1 or 0?

Hi Aladin,

a Yes or no, or even a 1 or 0

ADVERTISEMENT
It would be great if you could clarify by answering Aladin's question, but until then, there are 2 ways to go by this on a single line query that i can think of

1) Number of occurances

Excel Workbook
AB
1List 1Number of occurances in list 2
2ball1
3light1
4hoop0
5
6List 2
7cloud
8apple
9soccerball
10road
11trafficlight
12
Sheet2

2) Return the string which matches (Just the 1st one for now)

Excel Workbook
AB
1List 1String match
2ballsoccerball
3lighttrafficlight
4hoop
5
6List 2
7cloud
8apple
9soccerball
10road
11trafficlight
Sheet1

You can use vlookup instead of search

=IFERROR(VLOOKUP(A2,\$B:\$B,1,0),"")

paste in col C2 and drag it till the list1

Hi Puru.sve,

Thanks for your response but this will not perform the search i require it to do.

ADVERTISEMENT
Sheet2

 A B 1 List 1 Number of occurances in list 2 2 ball 1 3 light 1 4 hoop 0 5 6 List 2 7 cloud 8 apple 9 soccerball 10 road 11 trafficlight 12

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:196px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas
 Cell Formula B2 =IFERROR(COUNTIF( A7:A11,"*"&A2&"*"),"") B3 =IFERROR(COUNTIF( A8:A12,"*"&A3&"*"),"") B4 =IFERROR(COUNTIF( A9:A13,"*"&A4&"*"),"")

<tbody>
</tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

2) Return the string which matches (Just the 1st one for now)

Sheet1

 A B 1 List 1 String match 2 ball soccerball 3 light trafficlight 4 hoop 5 6 List 2 7 cloud 8 apple 9 soccerball 10 road 11 trafficlight

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:72px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas
 Cell Formula B2 =IFERROR(VLOOKUP("*"&A2&"*", A7:A11,1,0),"") B3 =IFERROR(VLOOKUP("*"&A3&"*", A8:A12,1,0),"") B4 =IFERROR(VLOOKUP("*"&A4&"*", A9:A13,1,0),"")

<tbody>
</tbody>

<tbody>
</tbody>

Hi dispelthemyth

This is almost what i am wanting, but instead of returning Soccerball next to ball from list one, i need ball to be returned next to soccerball in list 2.

Apologies all for not clearly outlining my problem

Hi Birtles,

Try this .. Array formula

={IF(SUM(IFERROR(MATCH(A2:A5,\$B\$2:\$B\$6,0),0))>1,"Yes","No")}

enter the formula with cntrl + shift + enter

Hi Aladin,

a Yes or no, or even a 1 or 0

1. Control+shift+enter, not just enter:
Rich (BB code):
``````=(SUM(MMULT(ISNUMBER(SEARCH(TRANSPOSE(IF(A1:A3="","##",A1:A3)),B1:B5))+0,
ROW(A1:A3)^0))>0)+0
``````
yields a diagnostic value: 1 meaning "Yes, there is at least one partial match", 0 the opposite.

2. Control+shift+enter:
Rich (BB code):
``````=SUM(IF(MMULT(ISNUMBER(SEARCH(TRANSPOSE(IF(A1:A3="","##",A1:A3)),B1:B5))+0,
ROW(A1:A3)^0)>0,1))
``````
yields a count of partially matching items.

Replies
30
Views
517
Replies
0
Views
115
Replies
14
Views
685
Replies
7
Views
229
Replies
0
Views
242

Threads
1,196,323
Messages
6,014,639
Members
441,833
Latest member
Rangerreeve

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

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