lookup multiple values in a cell and return true/false

[SriniML]

Hi -

I have a sheet with a column containing a Group name and another column with multiple group member ids in each cell separated with % symbol.

For eg:

A1 = Group A
B1 = 34543%456456%23475

I have another sheet that has group member ids, however they are not of the same order as sheet 1.

For eg:

A1 = 23475%34543%456456

Need to look up values from Sheet 2 in Sheet 1 and identify if a value exists or not.

for eg: In the above scenario, the value should return as True, because the member IDs are same, but just that they are not in same order.

Regards
Srinivas

 

[hemesh]

hello you can try in C1 of Sheet 1
=MATCH(B1,Sheet2!A:A,0) if value exists then it will give you a row number or else #n/a error is it ok or you still want true and false

 

[hemesh]

In case you want only true and false then try in c1 of sheet 1
=IF(IFERROR(MATCH(B1,Sheet2!A:A,0),FALSE),TRUE)

 

[SriniML]

Sorry - For some reason, this doesn't work for me. It may not be matching because, data in cell A1 of sheet 1 are not in the same order of data in cell A1 of Sheet 2.

Sheet - 1
A1 = Group A
B1 = 34543%456456%23475

Sheet - 2
A1 = 23475%34543%456456

Regards
Srinivas

 

[hemesh]

in sheet 1 where your data starts and ends(which you want to match) and in sheet 2 where your data starts and ends(data in which you want to look)

 

[markmzz]

Try this:

Array formula - use Ctrl+Shift+Enter and not only Enter to enter the formula

=SUMPRODUCT(--(MMULT(TRANSPOSE(--ISNUMBER(SEARCH("%"&TRIM(MID(SUBSTITUTE(B1,"%",REPT(" ",LEN(B1))),
ROW(INDIRECT("1:"&LEN(B1)-LEN(SUBSTITUTE(B1,"%",""))+1))*LEN(B1)-LEN(B1)+1,LEN(B1)))&"%",
"%"&TRANSPOSE(Sheet2!A1:A2)&"%"))),ROW(INDIRECT("1:"&LEN(B1)-LEN(SUBSTITUTE(B1,"%",""))+1))^0)=3))

Markmzz

 

[SriniML]

Hi Mark -

Thank you for your response. However could you please help me understand this formula so that i will know where should i put this formula.. sheet 2 or sheet 1 ? I had put this in Sheet 1 - C1, but i get 0's. Not sure what it means.

Regards
Srinivas

 

[markmzz]

Hi Mark -

Thank you for your response. However could you please help me understand this formula so that i will know where should i put this formula.. sheet 2 or sheet 1 ? I had put this in Sheet 1 - C1, but i get 0's. Not sure what it means.

Regards
Srinivas

First, a small modification in my formula.

Array formula - use Ctrl+Shift+Enter and not only Enter to enter the formula

In the Sheet1 cell C1 put this formula

C1-> =SUMPRODUCT(--(MMULT(TRANSPOSE(--ISNUMBER(SEARCH("%"&TRIM(MID(SUBSTITUTE(B1,"%",REPT(" ",LEN(B1))),
ROW(INDIRECT("1:"&LEN(B1)-LEN(SUBSTITUTE(B1,"%",""))+1))*LEN(B1)-LEN(B1)+1,LEN(B1)))&"%",
"%"&TRANSPOSE(Sheet2!$A$1:$A$4)&"%"))),ROW(INDIRECT("1:"&LEN(B1)-LEN(SUBSTITUTE(B1,"%",""))+1))^0)=
[COLOR=#ff0000]LEN(B1)-LEN(SUBSTITUTE(B1,"%",""))+1[/COLOR]))

Layout

[TABLE="width: 314"]
<colgroup><col width="47" style="width: 35pt; mso-width-source: userset; mso-width-alt: 1718;"> <col width="121" style="width: 91pt; mso-width-source: userset; mso-width-alt: 4425;"> <col width="27" style="width: 20pt; mso-width-source: userset; mso-width-alt: 987;"> <col width="42" style="width: 32pt; mso-width-source: userset; mso-width-alt: 1536;"> <col width="17" style="width: 13pt; mso-width-source: userset; mso-width-alt: 621;"> <col width="121" style="width: 91pt; mso-width-source: userset; mso-width-alt: 4425;"> <col width="42" style="width: 32pt; mso-width-source: userset; mso-width-alt: 1536;"> <tbody>[TR]
[TD="class: xl65, width: 47, bgcolor: transparent"]Group A[/TD]
[TD="class: xl65, width: 121, bgcolor: transparent"]34543%456456%23475[/TD]
[TD="class: xl66, width: 27, bgcolor: yellow, align: right"]1[/TD]
[TD="class: xl67, width: 42, bgcolor: transparent"]Sheet1[/TD]
[TD="class: xl65, width: 17, bgcolor: transparent"] [/TD]
[TD="class: xl65, width: 121, bgcolor: transparent"]23475%34543%456456[/TD]
[TD="class: xl67, width: 42, bgcolor: transparent"]Sheet2[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Group B[/TD]
[TD="class: xl65, bgcolor: transparent"]3454%4564%2347[/TD]
[TD="class: xl66, bgcolor: yellow, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"]99475%88543%336457[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Group C[/TD]
[TD="class: xl65, bgcolor: transparent"]3543%4456%2477[/TD]
[TD="class: xl66, bgcolor: yellow, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"]2075%3043%4058[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Group D[/TD]
[TD="class: xl65, bgcolor: transparent"]23475%345434[/TD]
[TD="class: xl66, bgcolor: yellow, align: right"]1[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"]345434%23475[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]********[/TD]
[TD="class: xl65, bgcolor: transparent"]***********************[/TD]
[TD="class: xl65, bgcolor: transparent"]****[/TD]
[TD="class: xl65, bgcolor: transparent"]*******[/TD]
[TD="class: xl65, bgcolor: transparent"]**[/TD]
[TD="class: xl65, bgcolor: transparent"]***********************[/TD]
[TD="class: xl65, bgcolor: transparent"]*******[/TD]
[/TR]
</tbody>[/TABLE]


Markmzz