Index Match with Multiple Criteria (and ranged numbers)-CodePudding

I'm not sure if Index Match is what I'm looking for, but I'm trying to return a certain value when various criteria is met. I'm not sure how to explain this, but the sample sheet is attached to better illustrate what I'm looking for.

Basically, there's a "schedule" sheet, and a "main data" sheet. I need it so when Col I of "main data" matches Col B of "schedule," and Col G of "main data" falls between the range of Col D and E in "schedule," then it should return the date found in Col C of "main data."

Eg. main data row 2 should return "Nov-10"; whereas row 49 would return "Nov-17"

Having a range here has me at a loss, and I'm not sure how to even approach it. Hopefully this all makes sense.

Thank you in advance!

sample sheet:

Blank is shown on last rows since G is not in between D and E of schedule

CodePudding user response：

use in N2:

=ARRAYFORMULA(IFNA(VLOOKUP(VLOOKUP(I2:I, 
 {UNIQUE(FILTER(schedule!B3:B, schedule!B3:B<>"")), 
  SEQUENCE(COUNTUNIQUE(schedule!B3:B), 1, 10000, 10000)}, 2, 0) G2:G, 
 {VLOOKUP(schedule!B3:B, {UNIQUE(FILTER(schedule!B3:B, schedule!B3:B<>"")), 
  SEQUENCE(COUNTUNIQUE(schedule!B3:B), 1, 10000, 10000)}, 2, 0)
  schedule!D3:D, schedule!C3:C}, 2, 1)))