Reflect the summary of inquiry for clients with query

[anneb_87]

Hi Experts!

Here is my expected output, I need to populate the in the query column the summary of Open Issue for those client with open issue.

Client Name	Query
A
B
C

Here is my source data

Client Name	With Open Issue?	Summary of Open Issue
A	Yes	Enrollment
B
C	Yes	Premium

Is this possible?

 

[hernantorres23]

Hi, there is a lot ways for do it.

• Pivot Table
• Power Query
• VLOOKUP
• INDEX
• Data/Filter

But let me show you it with an example:
In query column, put =IF(VLOOKUP (A1,'source_data'!A2:B4,2,FALSE)="Yes",VLOOKUP (A1,'source_data'!A2:B4,3,FALSE),"")

I'm sorry, but I'm writing from my cellphone.

 

[Peter_SSs]

See if this is what you mean.

Book1
	A	B	C
1	Client Name	Query
2	A	Enrollment
3	B
4	C	Premium
5
6
7	Client Name	With Open Issue?	Summary of Open Issue
8	A	Yes	Enrollment
9	B
10	C	Yes	Premium
Open Issue

Cell Formulas
Range	Formula
B2:B4	B2	=IF(VLOOKUP(A2,A$8:B$10,2,0)="Yes",VLOOKUP(A2,A$8:C$10,3,0),"")

 

[anneb_87]

Thanks Experts! This works

 

[anneb_87]

Oooppss I got some problem.. because the summary open issue for the multiple data of a given client

So if I will be using the vlookup the first value that it will get is the first row only of that client.. how should I fix the formula?

Updated source data

Client Name	Data	With Open Issue?	Summary of Open Issue
A	1​
A	2​	Yes	Enrollment
A	3​
B	1​
B	2​

 

[Peter_SSs]

Updated source data

This has a different structure to the earlier example. What would the results look like this time?

 

[anneb_87]

The result will be same I just updated the source data (from above)

Client Name	Query
A
B
C

 

[Peter_SSs]

The result will be same

So try this.

Book1
	A	B	C	D
1	Client Name	Query
2	A	Enrollment
3	B
4	C
5
6
7	Client Name	data	With Open Issue?	Summary of Open Issue
8	A	1
9	A	2	Yes	Enrollment
10	A	3
11	B	1
12	B	2
Open Issue (2)

Cell Formulas
Range	Formula
B2:B4	B2	=IF(COUNTIFS(A$8:A$12,A2,C$8:C$12,"Yes"),INDEX(D$8:D$12,MATCH(A2&"|Yes",INDEX(A$8:A$12&"|"&C$8:C$12,0),0)),"")

 

[anneb_87]

It works perfectly fine! You are amazing!!

 

[Peter_SSs]

It works perfectly fine!

You're welcome. Thanks for the confirmation.