Reflect the summary of inquiry for clients with query

anneb_87

New Member
Joined
Jun 13, 2018
Messages
43
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 NameQuery
A
B
C


Here is my source data

Client NameWith Open Issue?Summary of Open Issue
AYesEnrollment
B
CYesPremium

Is this possible?
 

hernantorres23

Board Regular
Joined
Nov 21, 2019
Messages
104
Office Version
365, 2016
Platform
Windows, Web
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.
 
Last edited:

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
41,774
Office Version
365
Platform
Windows
See if this is what you mean.

xl2bb.xlam
ABC
1Client NameQuery
2A
3B
4C
5
6
7Client NameWith Open Issue?Summary of Open Issue
8AYesEnrollment
9B
10CYesPremium
Open Issue
Cell Formulas
Range(s)Formula
B2:B4B2=IF(VLOOKUP(A2,A$8:B$10,2,0)="Yes",VLOOKUP(A2,A$8:C$10,3,0),"")
 

anneb_87

New Member
Joined
Jun 13, 2018
Messages
43
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 NameDataWith Open Issue?Summary of Open Issue
A
1​
A
2​
YesEnrollment
A
3​
B
1​
B
2​
 

anneb_87

New Member
Joined
Jun 13, 2018
Messages
43
The result will be same I just updated the source data (from above)
Client NameQuery
A
B
C
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
41,774
Office Version
365
Platform
Windows
The result will be same
So try this.

xl2bb.xlam
ABCD
1Client NameQuery
2A
3B
4C
5
6
7Client NamedataWith Open Issue?Summary of Open Issue
8A1
9A2YesEnrollment
10A3
11B1
12B2
Open Issue (2)
Cell Formulas
Range(s)Formula
B2:B4B2=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)),"")
 

Forum statistics

Threads
1,078,070
Messages
5,338,035
Members
399,198
Latest member
PsycholoJackal

Some videos you may like

This Week's Hot Topics

Top