Hi, for my business website I am making a product import sheet. I Have a column for product category number with numbers ranging from 1-66 randomly depending on what category they are on our website, from cells H2-H4455. In column M, I want to devise a formula to change this number to a "Category Name" so if i have the number 13 in cell H2, in M2 i want the word "Tiles" to display or number 44 i want "Showers" display. How do i do this, the full breakdown of category numbers are as follows: "Tiles" = 1-39, 53, 67

"Bathrooms" = 42, 43, 48, 52, 59, 60

"Showers"= 44, 45, 49, 54, 55, 56

"Taps & Radiators"= 57, 61

"Bathroom Furniture"= 41

"Wooden Floors"= 62, 63, 64, 65, 66

"Accessories"= 40, 46, 47, 50, 51, 58Thanks in advance

VLOOKUP should work for you. VLOOKUP (

lookup_value, table_array, col_index_num, [range_lookup])Put your numbers in a range, say A1:A66. In B1:B66 put the corresponding category designations.

In M2, enter this:

=VLOOKUP(H2,$A$1:$B$66,2,0)

What this will do is look up value from H2 in A1:A66 and return the value in the same row of B1:B66.

You should do some reading on the VLOOKUP function. The key point is that the

col_index_numargument (the "2" in the formula above) does not directly refer to Column B, it refers to the 2nd column of thetable_array. In other words, if yourtable_arraywas in F1:G66, you would still use 2 because G is the 2nd column of an F:G array.https://support.office.com/en-us/ar...

This range(

table_array) can be placed anywhere, even on another sheet. Just make sure that you reference it correctly in your function.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03

I thought I'd offer a few more tips about the VLOOUKP function. One of the major advantages of the VLOOKUP function is the ability to make changes to your data without having to change any formulas.

For example, if you decide that item #50 fits the Bathroom Furniture category better than Accessories, you can simply change the

table_arrayand not have to touch any formulas that refer to that table.In addition, you currently have 66 items in your inventory. You can allow for expansion by simply changing the formula to read something like this:

=VLOOKUP(H2,$A$1:$B$200,2,0)

As you add inventory items in the future, you can just add them to the bottom of the table and the VLOOKUP formula will find them. Again, no need to change any formulas.

One last item:

As you noted, your items are kind of "random" in their numbering scheme, which means that your

table_arrayhas to contain a each and every number, even though the categories repeat. One feature of the VLOOKUP function is the ability to find a value within arangeof values, e.g 1-100. The[range_lookup]argument would allow you to shorten yourtable_arrayto just the number of categories if the numbers were sequential within each category. It's not a big deal, but it might make your table maintenance a little easier. In other words, your table might look like this:A B 1 100 Tiles 2 200 Bathrooms 3 300 Showers etc.With the following formula, your result would be "Tiles" for any H2 value from 100-199, Bathrooms for 200-299, etc.

=VLOOKUP(H2,$A$1:$B$66,2,

1)The

[range_lookup]argument of 1 tells Excel to return the value from column 2 within arangeinstead of looking for an exact match.This "grouping" would also allow you to add inventory (up to 100 items per category in this case) just by assigning a number within the correct range to the additional items.

Of course, it would also be easier to remember that any value that starts with a 1 is a "Tile", as opposed to trying to remember a whole bunch of randomly assigned numbers. ;-)

Obviously you know your business processes better than I, so I'm just tossing that out so that you know that the feature exists.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Ask Your Question

Weekly Poll

Do you think Monopoly should update its pieces?

Discuss in The Lounge

Poll History