# Array formula with index match to return array of matches

I've been working at this for a bit. I have tried many times different formula combos, using VLOOKUP to using INDEX & MATCH, even with an ARRAYFORMULA.

What I'm attempting to do is match a territory to return a state. Then be able to copy the formula down.

As of now when I copy the formula downwards it can only return the first find in the column.

Where did I go wrong? I'm sure this far too simple and I'm overthinking the entire thing.

I was hoping for something like the following formula to work:

``````=ArrayFormula(index(States,Match(TER 1,Territory,0)))

=ArrayFormula(index(\$D\$3:\$D\$53,MATCH(\$B\$3,\$C\$3:\$C\$53,0)))
`````` I tried this as well:

``````=ArrayFormula(TEXTJOIN(",",TRUE,if(C3:C53=J3,D3:D53,"")))
``````

But this only returned the array of course.

AZ, Arizona,ID, Idaho,ME, Maine,NV, Nevada,OH, Ohio,UT, Utah

• Welcome. Please try to avoid cross-posting in the future. Also, please remember that as per site guidelines when an answer addresses your question, accept it and even upvote it so others can benefit as well. – marikamitsos Mar 7 at 3:40

Try a query instead. In cell `F4` try the following formula:

``=IFERROR(QUERY(\$C\$3:\$D\$52,"select D where C='"&G\$3&"'"),"")``

You can even drag the formula from `F3` to the right and all of the rest will auto-fill. Functions used:

• Looks good but when I did it, it didn't work for me. Not sure why. But I did come up with another solution. Thank you for pointing me in the right direction! btw, is it absolutely imperative to wrap it in "IFERROR"? – handpaintedstudio Mar 7 at 22:21
• The formula works just fine. "Looks good but when I did it, it didn't work for me.". The only possible way is if you did not copy paste my formula correctly. Please try this: Select the formula I gave you. Right click and copy. Paste in cell `F3`. Practice. The two formulas are identical, calculating the exact same thing. As for the link you posted, it is the link in my post. The difference between the 2 posts is accurately depicted here. Still. Glad you worked it out :) – marikamitsos Mar 8 at 5:52

So, I was pointed in the right direction by marikamitsos, thank you! However, I found a slightly different solution.

``````=QUERY(\$C\$3:\$D\$53,"select D where C='TER 2'")
`````` 