INDEX/MATCH using multiple static lookup_values from drop down

Hello, I am trying to do an INDEX/MATCH to return a value based on multiple lookup_values.

The workbook is attached, and what I am trying to accomplish is this:

If 50, 75, or 90 are selected from Column D drop down of this (Main) worksheet, then return Column A, B, and C values for those rows to the Report worksheet.

So in my sample spreadsheet, John, Jim, and Jack's information should be returned on the Report worksheet because their Column D shows 50, 75, and 90.
Alice, Wanda, and Cindy's information should not be returned on the Report worksheet because their column D shows Budget Only, Contacting, and 100.

My current INDEX/MATCH formula is =INDEX(Main!$C2,MATCH(50,Main!$D2),0), but this only works when column D has 50 chosen because I have only asked MATCH to return info if it finds something with 50 because I do not know how to ask it to search for multiple lookup_values.

So, as you will see, only John's information is returned on the report worksheet, but Jim and Jack's are not.

Matchmult.xlsx

Thank you!

It is not working for me on my production sheet but that's probably because I don't quite understand what's happening yet . I'll look into the functions you're using to understand it better, but in the meantime are you saying that the match feature doesn't allow multiple lookup_values? For example, something like: =INDEX(Main!$C2,MATCH(50+75+90,Main!$D2),0)

Thanks again.

Originally Posted by FDibbins

Instead of trying to make a formula for this, have you looked at using filters? Click on the table, go to Filter and Sort/Filter, then click the "down" arrow" on "change" and select/unselect what you want to show

I don't quite understand, I am trying to take certain values from one sheet, based on criteria, and automatically re-populate them on another sheet. Can I do that with filters?

I use for my day to day work which is based on the quarterly happenings of my job, however once a week I am asked to provide a report that looks at just the current month to date. Anything labelled 50, 75, or 90, has a chance of being resolved this month.

I would like the items that have a chance of being resolved this month to populate on the reports worksheet so my bosses can have one thing to look at without going through all of the sheets that I use day to day.