Forum Discussion
How to lookup and retrieve data using multiple data points
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 |
=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.
2 Replies
=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.
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.
