Array formula with INDEX MATCH to return array of matches

0
votes

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)))

See what my formula is as of now.

1
google-sheetsmatchvlookuparray-formulasgoogle-sheets-query
I attempted to use this: =ArrayFormula(TEXTJOIN(",",TRUE,if(C3:C53=J3,D3:D53,""))) but it only returned the array, obvously, as "AZ, Arizona,ID, Idaho,ME, Maine,NV, Nevada,OH, Ohio,UT, Utah" – handpaintedstudio
I think you are definitely over-complicating it. Can you share a link to a sample sheet so it's a little more clear what you're after? – MattKing
1) Share your spreadsheet with no sensitive info 2) Show what is the expected result? – Jeff Rush

1 Answers

1
votes

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: