SOLVED

How to lookup and retrieve data using multiple data points

Occasional Contributor

I need to determine whether an agent offers a particular service, but there are multiple agents sharing names across locations offering different services. I would like the solution to be able to reference more agents as their services are added.

 

 

MY MATRIX:

AGENTSTATESERVICE
SlapHappyTEXASSLAPS ON FRIDAY
SlapHappyCALIFORNIADOES NOT SLAP
SlapHappyMEXICOPENDING AGREEMENT
SlapTrapTEXASSLAPS ALL DAY
MRSLAPPYTEXASDOES NOT SLAP
MRSLAPPYLOUISIANAWILL ONLY SLAP IN LA


How do I build a formula that will account for those multiple variables and be able to auto fill services as they populate?

 

AGENTSTATE
SlapHappyTEXAS
SlapHappyCALIFORNIA
SlapHappyMEXICO
SlapTrapTEXAS
MRSLAPPYTEXAS
MRSLAPPYLOUISIANA
2 Replies
best response confirmed by xen900 (Occasional Contributor)
Solution

@xen900 

=TEXTJOIN(", ",,FILTER($C$3:$C$18,($A$3:$A$18=E2)*($B$3:$B$18=F2)))

Is this similar to what you are looking for? The formula is in cell G2 and filled down to G7. The ranges of the formula can be adapted as required.

textjoin.JPG

 

 

Thank you so much!

 

*Edit - OMG it also allows you to add multiple services for the same agent. You're literally heaven sent... this will make my day so much easier.