VBA to return multiple values based on cases

[pablo_max3045]

Hello all,
I have a rather hard (for me) excel task I need to solve, but I am not sure where to start.
I have two sheets in my workbook. One showing different frequency bands with can be set to Yes/No supported and a 2nd sheet showing 3 different cases.

Based on which value is there, I need to filter the list and return the band into the low/mid/high colums

Supported	Band	Duplex Mode	Up lowest	Up highest
Yes	7	FDD	2500	2570
Yes	8	FDD	880	915
Yes	11	FDD	1427.9	1447.9
Yes	12	FDD	699	716
Yes	13	FDD	777	787
Yes	18	FDD	815	830
Yes	19	FDD	830	845
Yes	20	FDD	832	862
Yes	71	FDD	663	617

For example, for case 1 I need to filter supported to true. Then filter "up lowest" to find the lowest value below 1000 and then return the value from band in the "LOW" column. 71 for example.
For High column, I need to do the same thing, except filter for the highest value below 1000 this time from "Up Highest" and return again the value from band. 8 for example.
I need to do that down the list for each low / mid / high for every test number.
I know this is something which VBA is made for, but ive not a clue how to do it.

Test number 1	CASE	Low	Mid	High
Test number 2	1	71​		8
Test number 3	1
Test number 4	2
Test number 5	2

 

[pablo_max3045]

My assumptions is that I need to run a loop for Low, if there is something in column A for the test number. Maybe with a find last row or something. Then have 3 separate paths for the cases. Then do the special filtering for each case and return the result to column C.
Then make a new loop for columns D and E (mid / high)