Hello, I'm having trouble understanding what function I should use to do lookups when there is more than one matching value. I know how to use vlookup, at least well enough to know it won't work in this case. Here's what I'm trying to do:
On one sheet (sheet1) I have a dropdown multi-select with about 80 different options. On another sheet (sheet2) I have all 80 options listed as rows with additional info added such as contact info. When a user fills out a form on sheet1 and selects multiple options, I would like to return ALL values matching everything they list.
For example, a user selects "Apple" and "Banana" in the multi-select dropdown on sheet1. Sheet2 has this info:
Apple, Red, redapple@gmail.com
Apple, Green, greenapple@gmail.com
Banana, Yellow, banana@gmail.com
I want to lookup Apple and Banana, and return "Red, Green, Yellow" and "redapple@gmail.com, greenapple@gmail.com, banana@gmail.com" to the corresponding row in sheet1.
Hopefully this example makes sense. Can someone help me understand how this works?
Thank you!