How to autopopulate a market column in Excel with all possible store sites within that market?

Ashley LeQuieu 20 Reputation points
2024-04-05T17:29:55.1066667+00:00

I work with large amounts of data in Excel and am wanting to autopopulate the "market" column with all retail store numbers. I am unsure which formula would be best to use for this.

My data report does not come with the Market column per its source, so I am wanting to find a way to have that column populate with the corresponding market without having to manually sort through each line and add it.

For example:

(small sample data - I have several markets and store numbers to work with)

MarketSite Number112, 10, 123, 235, 56, 86265, 78, 887, 246, 421, 987, 2435Looking for a formula similar to this:

If Site Number = 12, 10, 123, 235, 56, or 86, then Market will populate to "1"

Excel
Excel
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
1,467 questions
0 comments
Accepted answer
  1. Jiajing Hua-MFST 4,825 Reputation points Microsoft Vendor
    2024-04-08T08:09:31.87+00:00

    Hi @Ashley LeQuieu

    Please check following steps:

    • List the site number that you want to compare in a column.
    • Select the range for Market column.
    • On my sample below, enter formula =IF(A2="","",COUNTIF(E$2:E$7,A2)) in formula bar. Please do not press Enter key.
    • Press Ctrl + Enter. User's image

    ---- Update ------

    The formula =IFNA(VLOOKUP(G2,$D$2:$E$77,2,FALSE),"") is based on Barry Schwarz's suggestion.

    enter image description here

    If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Barry Schwarz 1,946 Reputation points
    2024-04-06T01:30:10.07+00:00

    If I understand correctly, you want to determine if a particular value (Site Number) appears in a particular array (a list of MarketSite Numbers). If the value does appear, you want to set another cell to 1. If it does not, then yu set that other cell to something else (possibly blank)

    It seems to me the MATCH or XMATCH function will answer the appearance question. If you embed the function inside an IF(ISNA(MATCH(...))," ",1)construction, it should perform as described above.

    0 comments