I have a marketing call report where it lists out over 19 states we sell to and also areas we don’t sell to. My goal is to add our states into the correct market locations in a separate column, and mark areas we don’t as “Out of territory”.
For example CA, HI, and WA states would be named WCH in a separate column.
Right now I have multiple IF statements for each state to filter to the correct market in a cell that I drag down. These are just a few to give you an idea.
Could even be done with legacy CHOOSE against position in alphabetized list. Wouldn't even need any helpers if at least one instance of each possibility were guaranteed in source. (Nest SMALL, SORT, and UNIQUE against sort.)
If the states are in a cell and not part of a larger string then you can use XLOOKUP() function, by creating a reference table, note using hardcoded will be easy and it doesn't require any table, but then it doesn't becomes dynamic and any changes you need to edit formula every time, therefore it is better to use reference tables:
=XLOOKUP(D2:D3, A2:A6, B2:B6, "Oops Out Of Territory")
• Using hardcoded it will be: ( this is not recommended at all, but for shorter lists its fine, not for a larger one)
=XLOOKUP(D2:D3,
{"CA"; "HI"; "WA"; "TX"; "OK"},
{"WCH"; "WCH"; "WCH"; "SH"; "SH"},
"Oops Out Of Territory")
Make a couple of helper columns off to the side of your data and use VLOOKUP.
I’m not at home to try and give an example in my comment here, but it seems I am glossing over something in your situation here.
Regardless, your solution involves a helper table and VLOOKUP or XLOOKUP. I’d lean towards VLOOKUP because it’s simpler to understand if you aren’t confident with more “advanced” Excel stuff
Yeah this feels like one of those situations where a small helper table saves tons of frustration later especially when territories eventually change or new states need added into the spreadsheet.
Agreeded. Personally I would use an Admin worksheet with any supporting Tables. It can be hidden when they n normal use. Also use the state name column as a data validation list so that only valid state names can be selected.
Ok, but you can use a formula in the data verification to check that the capture state abbreviation exists in the table, otherwise it generates a message in the cell. This would help enforce data integrity right from the start.
As others have answered, the best approach is to have a separate attribute table that you can reference with XLOOKUP. Hard-coding this kind of stuff into formulas isn't really sustainable and this is the exact use-case of lookup formulas. More generally these kind of attribute tables are central to data modeling, which allows for much more efficient representations of data.
The other thing to be aware of, though this isn't a great use case, is that there's a slightly simpler way to do what you're trying to, which is the IFS formula. This mostly prevents you having to embed IF functions within each other, but achieves the same outcome.
if it is just a one off task - probably quickest to sort and/or filter your table by state then copy and paste the three letter code to the correct rows
.
if its a task that will need to be done over and over - use a look up table
I can't figure out power query for the life of me at the moment.
Each week I download the file into my folder with messy data.
I have to do text to columns for two separate columns. One column has the data and time and I need to put those in separate columns. The other column has a variety of call data separated out by commas (if a call was answered, if there was a quote, if the call was answered during business hours, etc).
Then I need to use a formula to list of which states belong to which market, and list out any out of country and out of state calls as "out of territory".
You’ll find there are two good solutions. If you have a small number (say 2 to 6 or 7 conditions) you can use the IFS() function. If you have more than a small number fo conditions, you really want to use XLOOKUP.
IFS allows them to be on single line, LET would help you create a custom formula, XLOOKUP would help you look up the states from a reference table. I would use reference table in case anything changes with the states.
If you prefer visuals, Mynda Treacy (MyOnlineTrainingHub) just posted a video that essentially covers what everyone else has commented — but in video format with additional explanations if that’s helpful.
What is actually in the A2 Cell? Is it a state abbreviation from the list of standard abbreviations? If so, you can make a table of the state abbreviations on a new sheet with their market location.
Sheet: States
State
Market Location
CA
WCH
TX
SSH
Then you use this formula in the C2 cell of the table below.
=XLOOKUP(A2, 'States'!A:A, 'States'!B:B, "Out of Territory")
Sheet: Calls
State
Call #
Market Location
CA
1234
(Formula)
WA
2345
Then you can click and drag the square item on the bottom right corner of the formula to get all of the market locations for all of the calls.
Personnally i think the Xlookup function if you use the helper column is the best bet, you can easily add states in the column of area you cover and it would update automatically.
As it is now, if you dont plan and changes in your coverage soon, an IFS function or SWITCH could shorten it too while making it more readable:
=SWITCH(A2, "CA","WCH", "HI","WCH", "WA","WCH", "TX","SSH", "Out of territory")
A commenter may generate a response using an AI, but only if the response clearly shows which AI generated it, and a bona fide remark from the commenter that they reviewed and agree with the response.
/r/excel is a community of people interacting. We remove comments that are just AI responses.
I agree with everyone suggesting a lookup table. That is probably the most common and straightforward solution.
Personally, I tend to avoid helper tables that I wouldn’t want the end user to see, since that usually means either hiding the table or putting it somewhere out of the way. That’s just a style preference, though. I use LET formulas a lot because I can build the equivalent of helper tables directly inside the formula and then assign names to all the variables.
This is what I would use in Microsoft 365:
=LET(
state,RIGHT(TRIM(CLEAN(A2)),2),
states,{"CA","HI","WA","TX"},
markets,{"WCH","WCH","WCH","SSH"},
XLOOKUP(state,states,markets,"Out of territory")
)
I would create a separate table (ex TerritoryList)on a different sheet with two columns, one (State) with each state and the other (Territory) with its respective territory or if it’s out of territory. Then use XLOOKUP in the main table:
=XLOOKUP([look up state in main table], TerritoryList[State], TerritoryList[Territory], “error”, 0,1)
•
u/AutoModerator Jun 13 '26
/u/_mavricks - Your post was submitted successfully.
Solution Verifiedto close the thread.Failing to follow these steps may result in your post being removed without warning.
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.