Help Seaching array

[Birtles]

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

 

[puru.sve]

You can use vlookup instead of search

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

paste in col C2 and drag it till the list1

 

[Aladin Akyurek]

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?

 

[Birtles]

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

 

[dispelthemyth]

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

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

Excel Workbook
	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
Sheet1

 

[Birtles]

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.

 

[Birtles]

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

 

[puru.sve]

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

 

[Aladin Akyurek]

Hi Aladin,

a Yes or no, or even a 1 or 0

1. Control+shift+enter, not just enter:

=(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:

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