Suggestion for returning data based on selected criteria
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
-
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.
Answers
-
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.
-
Omg, Paul you are awesome!! Thank you so much, that worked perfectly!
Help Article Resources
Categories
Check out the Formula Handbook template!
