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")

	A	B	C	D			A	B	C	D
1	ID	Result		tab1		1	ID	Col B		tab2
2	90001	No				2	90001	3(15), 2(18)
3	90002	Yes				3	90002	3(15), 2(18), 3(1)
4	90003	No				4	90003	3(15), 2(18)
5	90004	Yes				5	90004	3(1)
6	90005	No				6	90005	3(15), 2(18)
7	90006	Yes				7	90006	3(15), 2(18), 3(1)
8	90007	No				8	90007	3(15), 2(18)
9	90008	Yes				9	90008	3(1)
10	90009	No				10	90009	3(15), 2(18)
11	90010	No				11
12						12
***	*******	*******	**	*****	****	***	*******	***************	**	*****

<tbody>
</tbody>

Markmzz