Forum Discussion
Solved
IF/AND/OR formula
Good morning. I am new to using IF/AND/OR formulas and need assistance with a formula.
I am working on a sales report with customers in 14 different states and various cities. I am looking to see if I can create a formula that will capture the territory the customer should be assigned to.
For example, if ABC Supply located in Enfield, CT should be assigned to 5. If ABC Supply located in Madisonville, KY should be assigned to 10.
Thank you in advance for your help!
Create a lookup range like this, let's say on a sheet named List, in A2:B15.
And let's say that your sales report has the cities in C2 and down.
In D2 enter the formula =XLOOKUP(C2, List!$A$2:$A$15, List!$B$2:$B$15, "")
If you don't have Microsoft 365 or Office 2021, use the following instead:
=IFERROR(VLOOKUP(C2, List!$A$2:$B$15, 2, FALSE), "")
Fill down.
Create a lookup range like this, let's say on a sheet named List, in A2:B15.
And let's say that your sales report has the cities in C2 and down.
In D2 enter the formula =XLOOKUP(C2, List!$A$2:$A$15, List!$B$2:$B$15, "")
If you don't have Microsoft 365 or Office 2021, use the following instead:
=IFERROR(VLOOKUP(C2, List!$A$2:$B$15, 2, FALSE), "")
Fill down.
Thank you HansVogelaar for your reply. I appreciate a different way of tackling this. I wasn't even thinking about a VLOOPUP or reference list. Much appreciated!
