Returning a Value Based on Mixed Criteria

[Excel_77]

Hi All,

I have a list of unique IDs in column A, I want to check these against the unique IDs in column A of "tab2" and when there is a match I want column B of "tab2" checked for an occurrence of "3(1)". With column B the cells maybe mixed so some will have "3(15), 2(18)" in this occurrence I want a "No" returned, however if the cell has "3(15), 2(18), 3(1)" I want "Yes returned as there is a match for "3(1)".

How can I do this?

 

[Joe4]

For an entry in cell A1, try:

=IF(SUMPRODUCT(--('tab2'!A:A=A1),--ISNUMBER(FIND("3(1)",'tab2'!B:B)))>0,"Yes","No")

 

[markmzz]

Hi All,

I have a list of unique IDs in column A, I want to check these against the unique IDs in column A of "tab2" and when there is a match I want column B of "tab2" checked for an occurrence of "3(1)". With column B the cells maybe mixed so some will have "3(15), 2(18)" in this occurrence I want a "No" returned, however if the cell has "3(15), 2(18), 3(1)" I want "Yes returned as there is a match for "3(1)".

How can I do this?

Hi!

Try the formula below too in B2 (tab1) and copy down:

=IF(COUNT(FIND("3(1)",VLOOKUP(A2,'tab2'!$A$2:$B$10,2,0))),"Yes","No")


[TABLE="class: grid, width: 493"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD][/TD]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]ID[/TD]
[TD]Result[/TD]
[TD][/TD]
[TD]tab1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]ID[/TD]
[TD]Col B[/TD]
[TD][/TD]
[TD]tab2[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="align: right"]90001[/TD]
[TD]No[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD="align: right"]90001[/TD]
[TD]3(15), 2(18)[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD="align: right"]90002[/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[TD="align: right"]90002[/TD]
[TD]3(15), 2(18), 3(1)[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD="align: right"]90003[/TD]
[TD]No[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]4[/TD]
[TD="align: right"]90003[/TD]
[TD]3(15), 2(18)[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD="align: right"]90004[/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5[/TD]
[TD="align: right"]90004[/TD]
[TD]3(1)[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD="align: right"]90005[/TD]
[TD]No[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]6[/TD]
[TD="align: right"]90005[/TD]
[TD]3(15), 2(18)[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD="align: right"]90006[/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]7[/TD]
[TD="align: right"]90006[/TD]
[TD]3(15), 2(18), 3(1)[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD="align: right"]90007[/TD]
[TD]No[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]8[/TD]
[TD="align: right"]90007[/TD]
[TD]3(15), 2(18)[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD="align: right"]90008[/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]9[/TD]
[TD="align: right"]90008[/TD]
[TD]3(1)[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD="align: right"]90009[/TD]
[TD]No[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]10[/TD]
[TD="align: right"]90009[/TD]
[TD]3(15), 2(18)[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD="align: right"]90010[/TD]
[TD]No[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]***[/TD]
[TD]*******[/TD]
[TD]*******[/TD]
[TD]**[/TD]
[TD]*****[/TD]
[TD]****[/TD]
[TD]***[/TD]
[TD]*******[/TD]
[TD]***************[/TD]
[TD]**[/TD]
[TD]*****[/TD]
[/TR]
</tbody>[/TABLE]


Markmzz