# 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. Mar 7, 2020 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"? Mar 7, 2020 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 :) Mar 8, 2020 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'")
`````` 