I am trying to automatically categorize location numbers (plants) into
markets. For example, locations 001, 010, 872, 301 and 400 are Market
4..... and locations 210, 300, 501, 777, and 898 are Market 5 ..... and
locations 255, 755, 601, 701, and 901 are Market 6. I'd like to have the
formula automatically type "4", "5" or "6" in the "Market" column based on
the location number. So it would look like this where the market column is
filled automatically.

Location Market
001 4
755 6
777 4
901 6

I appreciate any advice that you may have. Thank you!

I did the following

I formatted Column A as text
I entered your locations in A1:A13 (for example)
I entered your markets in B1:B13 corresponding to the value in A.

I formatted Column E as text.
In F1 I entered: =VLOOKUP(E1,A1:B13,2,FALSE)

If you type a location in E1 then F1 will contain the market.

"SB777" wrote:

I am trying to automatically categorize location numbers (plants) into
markets. For example, locations 001, 010, 872, 301 and 400 are Market
4..... and locations 210, 300, 501, 777, and 898 are Market 5 ..... and
locations 255, 755, 601, 701, and 901 are Market 6. I'd like to have the
formula automatically type "4", "5" or "6" in the "Market" column based on
the location number. So it would look like this where the market column is
filled automatically.

Location Market
001 4
755 6
777 4
901 6

I appreciate any advice that you may have. Thank you!

Custom Format cells in column A as 000

In B1:
=IF(OR(A1={1,10,872,301,400}),4,IF(OR(A1={210,300, 501,777,898}),5,IF(OR(A1={255,755,601,701,901}),6, "Not available")))

Copy down as far as required


"SB777" wrote:

I am trying to automatically categorize location numbers (plants) into
markets. For example, locations 001, 010, 872, 301 and 400 are Market
4..... and locations 210, 300, 501, 777, and 898 are Market 5 ..... and
locations 255, 755, 601, 701, and 901 are Market 6. I'd like to have the
formula automatically type "4", "5" or "6" in the "Market" column based on
the location number. So it would look like this where the market column is
filled automatically.

Location Market
001 4
755 6
777 4
901 6

I appreciate any advice that you may have. Thank you!

Hi SB777:

If you have your LocationNumbers are in column A, corrresponding Markets are
in column B, then with a LocationNumber in cell D4, you can compute the
corresponding Market using the following INDEX_MATCH formula ...

=INDEX(B:B,MATCH(D4+0,A:A,0))


"SB777" wrote:

I am trying to automatically categorize location numbers (plants) into
markets. For example, locations 001, 010, 872, 301 and 400 are Market
4..... and locations 210, 300, 501, 777, and 898 are Market 5 ..... and
locations 255, 755, 601, 701, and 901 are Market 6. I'd like to have the
formula automatically type "4", "5" or "6" in the "Market" column based on
the location number. So it would look like this where the market column is
filled automatically.

Location Market
001 4
755 6
777 4
901 6

I appreciate any advice that you may have. Thank you!