Index Match

Options

My first attempt to use this function.

I have a worksheet that has a column (Category). Properties set for dropdown / single select. Rather laborious to manually click on every cell and select.

Two other columns contain Product description column and part number column. The dropdown column places the item in a category.


I have a separate sheet. Each row is populated with description, part number, and category.

Apparently I can do an index/match function in the current dropdown cell (=index....) where it looks in the reference sheet to populate the correct category.

How would that formula be structured?


Thanks in advance.

Answers

  • James Keuning
    James Keuning ✭✭✭✭✭
    Options

    I am not totally sure of your configuration, but here is how I do it. I always create a column called match, and I put my MATCH formula in there. That will return a number.

    The MATCH is pretty straight forward, but if you can't get a number in response, you know you have it wrong. The MATCH is just a reference to the field in the current row that you want to find, the foreign range, and then the sort instruction, which I find is usually a 0. That number relates to how the foreign range is sorted.

    And then I create another column for the INDEX. That field has the foreign range, a comma, followed by a reference to the MATCH field.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!