2

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

table using my current index match arrayformula that doesn't work when dragging it down.

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

Improve this question
1
  • 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
    Commented Mar 7, 2020 at 3:40

2 Answers 2

Reset to default
1

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.

use a query instead of index match or vlookup

Functions used:

Improve this answer
2
  • 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
    Commented 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 :)
    – marikamitsos
    Commented Mar 8, 2020 at 5:52
1

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

Solution to my question. I needed "QUERY" not "INDEX"

This article helped a lot > Google Sheets Query

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.