Create Drop Down List Based on Manually Entered Criteria

In N2 of your data table sheet:

Excel 2016 (Windows) 32 bit

	N
2	=IFERROR(INDEX(Table1[[#All],[DETAILS]],SMALL(IF(Table1[RATING]=DROPDOWN!$B$1,ROW(Table1[DETAILS])),ROWS($N$1:N1))),"")

Sheet: DATA

... confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Drag this down to N5000.

Create a named range called List based on N2:N5000.

Select A4 on the dropdown sheet and create a data validation that uses =List as its source.

You can hide the helper column N if you want to.

In "DATA" sheet : where is "CASE QTY"

AVK - it's not needed. That will be calculated on the dropdown sheet from the base price on the data sheet. I am assuming that the OP only needs help with the dropdown, not the calculations.

ALIGW, thank you so much! That is almost exactly what I needed!

The only problem is that column "N" on the DATA sheet shows "Apple" (whose RATING is 1) no matter what value is in $B$2; and it doesn't show the first DETAILS value for the corresponding RATING (unless the RATING is 1).

When I first made the NAMED RANGE, List, it was giving me a lot of blank cells in the drop down list. The solution to that problem was to use the following as the source for the List named range:

=DATA!$N$2:INDEX(DATA!$N$2:$N$5000,SUMPRODUCT(--(DATA!$N$2:$N$5000<>"")))

Again, thank you so much! I've attached an updated version of the file with additional notes for anyone who would like to see the solution in action. It also has the vlookups for the other columns showing how the drop down selection fills the rest of the table.

ONE MORE QUESTION: What if I wanted the drop down to include values for all DETAILS whose RATING is less than or equal to the RATING in $B$2? How would that change the formula in the "N" column on the DATA sheet? For example, if the RATING was 2, it would populate column "N" with all of the DETAILS that have a RATING of 1 or 2 instead of just those with RATING 2.

It works perfectly for me on your workbook. I can't look now, but will try to later this afternoon, unless someone else chimes in.

Did you reactivate by pressing Ctrl, Shift and Enter after making the change to the formula in N2? I changed the formula in N2 to read:

Formula:

=IFERROR(INDEX(Table1[[#All],[DETAILS]],SMALL(IF(Table1[RATING]<=DROPDOWN!$B$1,ROW(Table1[DETAILS])),ROWS($N$1:N1))),"")

I then simultaneously pressed Ctrl, Shift and Enter and copied down to N500 and it worked as expected. Perhaps copying to N5000 is causing the issue.
Let us know if you have any questions.

You're Welcome. Thank you for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.