Aug 23 2022 09:38 AM
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:
AGENT | STATE | SERVICE |
SlapHappy | TEXAS | SLAPS ON FRIDAY |
SlapHappy | CALIFORNIA | DOES NOT SLAP |
SlapHappy | MEXICO | PENDING AGREEMENT |
SlapTrap | TEXAS | SLAPS ALL DAY |
MRSLAPPY | TEXAS | DOES NOT SLAP |
MRSLAPPY | LOUISIANA | WILL 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?
AGENT | STATE |
SlapHappy | TEXAS |
SlapHappy | CALIFORNIA |
SlapHappy | MEXICO |
SlapTrap | TEXAS |
MRSLAPPY | TEXAS |
MRSLAPPY | LOUISIANA |
Aug 23 2022 10:30 AM
Solution=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.
Aug 23 2022 11:35 AM - edited Aug 23 2022 11:39 AM
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.
Aug 23 2022 10:30 AM
Solution=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.