|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:
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.