Solved IF Function Help Required

February 20, 2016 at 04:42:30
Specs: Windows 7
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, 58

Thanks in advance

See More: IF Function Help Required

Report •

February 20, 2016 at 05:31:17
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:


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_num argument (the "2" in the formula above) does not directly refer to Column B, it refers to the 2nd column of the table_array. In other words, if your table_array was in F1:G66, you would still use 2 because G is the 2nd column of an F:G array.

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

Report •

February 20, 2016 at 06:51:04
✔ Best Answer
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_array and 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:


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_array has 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 a range of values, e.g 1-100. The [range_lookup] argument would allow you to shorten your table_array to 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

With the following formula, your result would be "Tiles" for any H2 value from 100-199, Bathrooms for 200-299, etc.


The [range_lookup] argument of 1 tells Excel to return the value from column 2 within a range instead 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.

Report •

Related Solutions

Ask Question