Suggestion for returning data based on selected criteria

@SPark ✭✭✭
edited 01/03/20 in Formulas and Functions

I'm looking for some guidance. I would like a cell to return text (category) based on selected criteria (sub category) -- screen shots below.

Example: If I select 'Wine' from the drop down, I would like the Category cell to return 'Food & Bev'.

I could do an IF statement, however, there are several subcategories in the drop down as well as several categories so I was hoping I was overlooking some kind of VLookup or something easier that could search a range for the categories to return a category. Hopefully that makes sense, any thoughts would be appreciated!!

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    You could set up a table such as below (column headers in bold).

    Table Sub Cat Table Cat

    Wine Food & Bev

    Cheese Food & Bev

    Table Cloth Material

    so on and so forth...........

    In the Category column, you would use a formula such as this:

    =INDEX([Table Cat]:[Table Cat], MATCH([Sub Category]@row, [Table Sub Cat]:[Table Sub Cat], 0))

    What this will do is look at the Sub Category column and pull the appropriate category from the Table Cat column.

    Using a table instead of the nested IF's allows a lot more flexibility to edit, remove, or add to the table as needed without having to open and edit a long, complex formula.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!