Suggestion for returning data based on selected criteria

@SPark

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!!

Sub Category.png

Category.png

Paul Newcome

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.

Paul Newcome

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.

@SPark

Omg, Paul you are awesome!! Thank you so much, that worked perfectly!

Paul Newcome

Happy to help! 👍️