Shortcut for nested IF function

tmckivergan

I am tying columns together in a sheet so that when the user selects a product from a dropdown from a cell, it will automatically populate the product's manufacturer. I can do this using the following:

=IF([Product Description]@row = "Product 1", "Company 1", IF([Product Description]@row = "Product 2", "Company 2", IF([Product Description]@row = "Product 3", "Company 3", IF([Product Description]@row = "Product 4", "Company 4"))))

However as there are about 100 possible products to choose from I am wondering if there is a faster or easier way to do this (e.g. not have a massive nested IF function)

Lee Joramo

You will want to do a VLOOKUP to another sheet that maintains these relationships of Product to Company. For example lets create a second sheet with two columns called "Product to Company"

Products     Company
----------------------
Product 1    Company 1
Product 2    Company 2
Product 3    Company 1
Product 4    Company 2
Product 5    Company 5

Now in your original sheet use the formula:

=VLOOKUP(Product@row, {Product to Company Range 1}, 2, false)

Notes:

• {Product to Company Range 1} is a "Reference" to the second sheet that needs to be created.
• see the VLOOKUP documents for more details https://help.smartsheet.com/function/vlookup

tmckivergan

For clarification, it is also not 1:1. For example, Products 1, 4 and 5 may all be made at Company 3.

Lee Joramo

You will want to do a VLOOKUP to another sheet that maintains these relationships of Product to Company. For example lets create a second sheet with two columns called "Product to Company"

Products     Company
----------------------
Product 1    Company 1
Product 2    Company 2
Product 3    Company 1
Product 4    Company 2
Product 5    Company 5

Now in your original sheet use the formula:

=VLOOKUP(Product@row, {Product to Company Range 1}, 2, false)

Notes:

• {Product to Company Range 1} is a "Reference" to the second sheet that needs to be created.
• see the VLOOKUP documents for more details https://help.smartsheet.com/function/vlookup

tmckivergan

Worked perfectly. Thank you!