Finding the value/match

[anneb_87]

Hi Experts,

What formula should I use if I want to validate if the value in Set B data and what are the values reflected in Set B data. The client wants to do it in excel not in macro. Is this possible in excel spreadsheet?

Here is the Set A data

		Expected Out Come	Expected Out Come
Client Name	Carrier	the Value is In Set B data?	Values in Set B data?
Client A	Kaiser Permanente	No	Anthem Blue Cross
Client A	Kaiser Permanente	No	Anthem Blue Cross
Client B	United HealthCare**	No	United Healthcare of California
Client B	United HealthCare**	No	United Healthcare of California
Client B	United HealthCare**	No	United Healthcare of California
Client B	United HealthCare**	No	United Healthcare of California
Client C	Aetna	Yes	Aetna
Client C	Aetna	Yes	Aetna
Client D	Aetna	No	Anthem Blue Cross | Lincoln Financial
Client D	Aetna	No	Anthem Blue Cross | Lincoln Financial
Client D	Aetna	No	Anthem Blue Cross | Lincoln Financial
Client D	Aetna	No	Anthem Blue Cross | Lincoln Financial
Client D	Aetna	No	Anthem Blue Cross | Lincoln Financial
Client D	Aetna	No	Anthem Blue Cross | Lincoln Financial
Client D	Aetna	No	Anthem Blue Cross | Lincoln Financial
Client D	Aetna	No	Anthem Blue Cross | Lincoln Financial
Client D	Aetna	No	Anthem Blue Cross | Lincoln Financial

Here is the Set B Data

Client Name	Carrier
Client A	Anthem Blue Cross
Client C	Aetna
Client D	Anthem Blue Cross
Client D	Lincoln Financial
Client D	Lincoln Financial
Client D	Lincoln Financial
Client D	Anthem Blue Cross
Client D	Anthem Blue Cross
Client D	Anthem Blue Cross
Client D	Lincoln Financial
Client D	Anthem Blue Cross
Client D	Lincoln Financial
Client B	United Healthcare of California

 

[GraH]

You can try this

[EDIT] I did not spot the repetition of client D before. How is that supposed to work? Any value is matching? Then belows' suggestion won't work.

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Client Name	Carrier	the Value is In Set B data?	Values in Set B data?				Client Name	Carrier
2	Client A	Kaiser Permanente	FALSE	Anthem Blue Cross				Client A	Anthem Blue Cross
3	Client A	Kaiser Permanente	FALSE	Anthem Blue Cross				Client C	Aetna
4	Client B	United HealthCare**	FALSE	United Healthcare of California				Client D	Anthem Blue Cross
5	Client B	United HealthCare**	FALSE	United Healthcare of California				Client D	Lincoln Financial
6	Client B	United HealthCare**	FALSE	United Healthcare of California				Client D	Lincoln Financial
7	Client B	United HealthCare**	FALSE	United Healthcare of California				Client D	Lincoln Financial
8	Client C	Aetna	TRUE	Aetna				Client D	Anthem Blue Cross
9	Client C	Aetna	TRUE	Aetna				Client D	Anthem Blue Cross
10	Client D	Aetna	FALSE	Anthem Blue Cross				Client D	Anthem Blue Cross
11	Client D	Aetna	FALSE	Anthem Blue Cross				Client D	Lincoln Financial
12	Client D	Aetna	FALSE	Anthem Blue Cross				Client D	Anthem Blue Cross
13	Client D	Aetna	FALSE	Anthem Blue Cross				Client D	Lincoln Financial
14	Client D	Aetna	FALSE	Anthem Blue Cross				Client B	United Healthcare of California
15	Client D	Aetna	FALSE	Anthem Blue Cross
16	Client D	Aetna	FALSE	Anthem Blue Cross
17	Client D	Aetna	FALSE	Anthem Blue Cross
18	Client D	Aetna	FALSE	Anthem Blue Cross
19
20
Sheet2

Cell Formulas
Range	Formula
C2:C18	C2	=B2=D2
D2:D18	D2	=INDEX($I$2:$I$14,MATCH(A2,$H$2:$H$14,0))

 

[GraH]

Repeated values in SET B seem redundant.
Depending on the Excel version, this can work? Notice, I changed some of your values to test the solution.

Book1
	A	B	C	D	E	F	G	H
1	Client Name	Carrier	the Value is In Set B data?	Values in Set B data?			Client Name	Carrier
2	Client A	Kaiser Permanente	FALSE	Anthem Blue Cross			Client A	Anthem Blue Cross
3	Client A	Kaiser Permanente	FALSE	Anthem Blue Cross			Client C	Aetna
4	Client B	United HealthCare**	FALSE	United Healthcare of California			Client D	Anthem Blue Cross
5	Client B	United HealthCare**	FALSE	United Healthcare of California			Client D	Lincoln Financial
6	Client B	United HealthCare**	FALSE	United Healthcare of California			Client D	Lincoln Financial
7	Client B	United HealthCare**	FALSE	United Healthcare of California			Client D	Lincoln Financial
8	Client C	Aetna	TRUE	Aetna			Client D	Anthem Blue Cross
9	Client C	Aetna	TRUE	Aetna			Client D	Anthem Blue Cross
10	Client D	Aetna	FALSE	Anthem Blue Cross;Lincoln Financial;Lincoln Financial;Lincoln Financial;Anthem Blue Cross;Anthem Blue Cross;Anthem Blue Cross;Lincoln Financial;Anthem Blue Cross;Lincoln Financial			Client D	Anthem Blue Cross
11	Client D	Lincoln Financial	TRUE	Anthem Blue Cross;Lincoln Financial;Lincoln Financial;Lincoln Financial;Anthem Blue Cross;Anthem Blue Cross;Anthem Blue Cross;Lincoln Financial;Anthem Blue Cross;Lincoln Financial			Client D	Lincoln Financial
12	Client D	Aetna	FALSE	Anthem Blue Cross;Lincoln Financial;Lincoln Financial;Lincoln Financial;Anthem Blue Cross;Anthem Blue Cross;Anthem Blue Cross;Lincoln Financial;Anthem Blue Cross;Lincoln Financial			Client D	Anthem Blue Cross
13	Client D	Aetna	FALSE	Anthem Blue Cross;Lincoln Financial;Lincoln Financial;Lincoln Financial;Anthem Blue Cross;Anthem Blue Cross;Anthem Blue Cross;Lincoln Financial;Anthem Blue Cross;Lincoln Financial			Client D	Lincoln Financial
14	Client D	Anthem Blue Cross	TRUE	Anthem Blue Cross;Lincoln Financial;Lincoln Financial;Lincoln Financial;Anthem Blue Cross;Anthem Blue Cross;Anthem Blue Cross;Lincoln Financial;Anthem Blue Cross;Lincoln Financial			Client B	United Healthcare of California
15	Client D	Aetna	FALSE	Anthem Blue Cross;Lincoln Financial;Lincoln Financial;Lincoln Financial;Anthem Blue Cross;Anthem Blue Cross;Anthem Blue Cross;Lincoln Financial;Anthem Blue Cross;Lincoln Financial
16	Client D	Aetna	FALSE	Anthem Blue Cross;Lincoln Financial;Lincoln Financial;Lincoln Financial;Anthem Blue Cross;Anthem Blue Cross;Anthem Blue Cross;Lincoln Financial;Anthem Blue Cross;Lincoln Financial
17	Client D	Aetna	FALSE	Anthem Blue Cross;Lincoln Financial;Lincoln Financial;Lincoln Financial;Anthem Blue Cross;Anthem Blue Cross;Anthem Blue Cross;Lincoln Financial;Anthem Blue Cross;Lincoln Financial
18	Client D	Aetna	FALSE	Anthem Blue Cross;Lincoln Financial;Lincoln Financial;Lincoln Financial;Anthem Blue Cross;Anthem Blue Cross;Anthem Blue Cross;Lincoln Financial;Anthem Blue Cross;Lincoln Financial
Sheet2

Cell Formulas
Range	Formula
C2:C18	C2	=ISNUMBER(MATCH(1,($G$2:$G$14=A2)*($H$2:$H$14=B2),0))
D2:D18	D2	=TEXTJOIN(";",TRUE,OFFSET($G$1,MATCH(A2,$G$2:$G$60,0),1,COUNTIF($G$2:$G$60,A2)))

 

[GraH]

Easier solution (in my book anyway) could be via PQ, where data in table B does not need to be sorted.

Book1
	A	B	C	D	E	F	G	H	I	J
2	tDataA			tDataB			PQ result
3	Client Name	Carrier		Client Name	Carrier		Client Name	Carrier	Values List B	On List B
4	Client A	Kaiser Permanente		Client D	Anthem Blue Cross		Client A	Kaiser Permanente	Anthem Blue Cross	FALSE
5	Client A	Kaiser Permanente		Client D	Anthem Blue Cross		Client D	Aetna	Anthem Blue Cross;Lincoln Financial	FALSE
6	Client B	United HealthCare**		Client D	Anthem Blue Cross		Client D	Lincoln Financial	Anthem Blue Cross;Lincoln Financial	TRUE
7	Client B	United HealthCare**		Client D	Lincoln Financial		Client D	Anthem Blue Cross	Anthem Blue Cross;Lincoln Financial	TRUE
8	Client B	United HealthCare**		Client A	Anthem Blue Cross		Client B	United HealthCare**	United Healthcare of California	FALSE
9	Client B	United HealthCare**		Client B	United Healthcare of California		Client C	Aetna	Aetna	TRUE
10	Client C	Aetna		Client D	Anthem Blue Cross
11	Client C	Aetna		Client D	Anthem Blue Cross
12	Client D	Aetna		Client D	Lincoln Financial
13	Client D	Lincoln Financial		Client D	Lincoln Financial
14	Client D	Aetna		Client D	Lincoln Financial
15	Client D	Aetna		Client C	Aetna
16	Client D	Anthem Blue Cross		Client D	Lincoln Financial
17	Client D	Aetna
18	Client D	Aetna
19	Client D	Aetna
20	Client D	Aetna
Sheet1

let
    Source = Excel.CurrentWorkbook(){[Name="tDataA"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Client Name", type text}, {"Carrier", type text}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Client Name"}, tDataB, {"Client Name"}, "tDataB", JoinKind.LeftOuter),
    #"Expanded tDataB" = Table.ExpandTableColumn(#"Merged Queries", "tDataB", {"Carrier"}, {"Carrier.1"}),
    #"Grouped Rows" = Table.Group(#"Expanded tDataB", {"Client Name", "Carrier"}, {{"Values List B", each Text.Combine(List.Distinct([Carrier.1]), ";"), type text}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "On List B", each Text.Contains([Values List B],[Carrier]))
in
    #"Added Custom"

 

[anneb_87]

Repeated values in SET B seem redundant.
Depending on the Excel version, this can work? Notice, I changed some of your values to test the solution.

Book1
	A	B	C	D	E	F	G	H
1	Client Name	Carrier	the Value is In Set B data?	Values in Set B data?			Client Name	Carrier
2	Client A	Kaiser Permanente	FALSE	Anthem Blue Cross			Client A	Anthem Blue Cross
3	Client A	Kaiser Permanente	FALSE	Anthem Blue Cross			Client C	Aetna
4	Client B	United HealthCare**	FALSE	United Healthcare of California			Client D	Anthem Blue Cross
5	Client B	United HealthCare**	FALSE	United Healthcare of California			Client D	Lincoln Financial
6	Client B	United HealthCare**	FALSE	United Healthcare of California			Client D	Lincoln Financial
7	Client B	United HealthCare**	FALSE	United Healthcare of California			Client D	Lincoln Financial
8	Client C	Aetna	TRUE	Aetna			Client D	Anthem Blue Cross
9	Client C	Aetna	TRUE	Aetna			Client D	Anthem Blue Cross
10	Client D	Aetna	FALSE	Anthem Blue Cross;Lincoln Financial;Lincoln Financial;Lincoln Financial;Anthem Blue Cross;Anthem Blue Cross;Anthem Blue Cross;Lincoln Financial;Anthem Blue Cross;Lincoln Financial			Client D	Anthem Blue Cross
11	Client D	Lincoln Financial	TRUE	Anthem Blue Cross;Lincoln Financial;Lincoln Financial;Lincoln Financial;Anthem Blue Cross;Anthem Blue Cross;Anthem Blue Cross;Lincoln Financial;Anthem Blue Cross;Lincoln Financial			Client D	Lincoln Financial
12	Client D	Aetna	FALSE	Anthem Blue Cross;Lincoln Financial;Lincoln Financial;Lincoln Financial;Anthem Blue Cross;Anthem Blue Cross;Anthem Blue Cross;Lincoln Financial;Anthem Blue Cross;Lincoln Financial			Client D	Anthem Blue Cross
13	Client D	Aetna	FALSE	Anthem Blue Cross;Lincoln Financial;Lincoln Financial;Lincoln Financial;Anthem Blue Cross;Anthem Blue Cross;Anthem Blue Cross;Lincoln Financial;Anthem Blue Cross;Lincoln Financial			Client D	Lincoln Financial
14	Client D	Anthem Blue Cross	TRUE	Anthem Blue Cross;Lincoln Financial;Lincoln Financial;Lincoln Financial;Anthem Blue Cross;Anthem Blue Cross;Anthem Blue Cross;Lincoln Financial;Anthem Blue Cross;Lincoln Financial			Client B	United Healthcare of California
15	Client D	Aetna	FALSE	Anthem Blue Cross;Lincoln Financial;Lincoln Financial;Lincoln Financial;Anthem Blue Cross;Anthem Blue Cross;Anthem Blue Cross;Lincoln Financial;Anthem Blue Cross;Lincoln Financial
16	Client D	Aetna	FALSE	Anthem Blue Cross;Lincoln Financial;Lincoln Financial;Lincoln Financial;Anthem Blue Cross;Anthem Blue Cross;Anthem Blue Cross;Lincoln Financial;Anthem Blue Cross;Lincoln Financial
17	Client D	Aetna	FALSE	Anthem Blue Cross;Lincoln Financial;Lincoln Financial;Lincoln Financial;Anthem Blue Cross;Anthem Blue Cross;Anthem Blue Cross;Lincoln Financial;Anthem Blue Cross;Lincoln Financial
18	Client D	Aetna	FALSE	Anthem Blue Cross;Lincoln Financial;Lincoln Financial;Lincoln Financial;Anthem Blue Cross;Anthem Blue Cross;Anthem Blue Cross;Lincoln Financial;Anthem Blue Cross;Lincoln Financial
Sheet2

Cell Formulas
Range	Formula
C2:C18	C2	=ISNUMBER(MATCH(1,($G$2:$G$14=A2)*($H$2:$H$14=B2),0))
D2:D18	D2	=TEXTJOIN(";",TRUE,OFFSET($G$1,MATCH(A2,$G$2:$G$60,0),1,COUNTIF($G$2:$G$60,A2)))

I'm back from vacation, thank for your response, one question in formula d2:d18, how come there is no reference for the carrier? But the input shows Carrier?

 

[Peter_SSs]

@anneb_87
I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

For example, if you have Excel 365, then for column D I would avoid the volatile function OFFSET with

21 01 04.xlsm
	A	B		D	E	F	G	H
1	Client Name	Carrier		Values in Set B data?			Client Name	Carrier
2	Client A	Kaiser Permanente		Anthem Blue Cross			Client A	Anthem Blue Cross
3	Client A	Kaiser Permanente		Anthem Blue Cross			Client C	Aetna
4	Client B	United HealthCare**		United Healthcare of California			Client D	Anthem Blue Cross
5	Client B	United HealthCare**		United Healthcare of California			Client D	Lincoln Financial
6	Client B	United HealthCare**		United Healthcare of California			Client D	Lincoln Financial
7	Client B	United HealthCare**		United Healthcare of California			Client D	Lincoln Financial
8	Client C	Aetna		Aetna			Client D	Anthem Blue Cross
9	Client C	Aetna		Aetna			Client D	Anthem Blue Cross
10	Client D	Aetna		Anthem Blue Cross|Lincoln Financial			Client D	Anthem Blue Cross
11	Client D	Lincoln Financial		Anthem Blue Cross|Lincoln Financial			Client D	Lincoln Financial
12	Client D	Aetna		Anthem Blue Cross|Lincoln Financial			Client D	Anthem Blue Cross
13	Client D	Aetna		Anthem Blue Cross|Lincoln Financial			Client D	Lincoln Financial
14	Client D	Anthem Blue Cross		Anthem Blue Cross|Lincoln Financial			Client B	United Healthcare of California
15	Client D	Aetna		Anthem Blue Cross|Lincoln Financial
16	Client D	Aetna		Anthem Blue Cross|Lincoln Financial
17	Client D	Aetna		Anthem Blue Cross|Lincoln Financial
18	Client D	Aetna		Anthem Blue Cross|Lincoln Financial
Check Values

Cell Formulas
Range	Formula
D2:D18	D2	=TEXTJOIN("|",1,UNIQUE(FILTER(H$2:H$14,G$2:G$14=A2,"")))