Index Match with Multiple Criteria (and ranged numbers)

user_16666598 0
Viewed 58

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: https://docs.google.com/spreadsheets/d/1KhdsJoD7d5gax5WhHJvXZZoiRRIG_oH6DgXx_snPqzI/edit#gid=1412972392

vlookup google-sheets sequence filter unique
2 Answers

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)))

enter image description here

update:

=ARRAYFORMULA(IF(G2:G="",,REGEXREPLACE(""&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, SORT(
 {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; (
  VLOOKUP(schedule!B3:B, {UNIQUE(FILTER(schedule!B3:B, schedule!B3:B<>"")), 
  SEQUENCE(COUNTUNIQUE(schedule!B3:B), 1, 10000, 10000)}, 2, 0)
 +schedule!E3:E+1)*{1, 0}}), 2, 1)), "^0$", )))

enter image description here

user_56326290

Copy this and drag downwards:

Formula:

=IFERROR(query({filter(schedule!B$3:E, I2 = schedule!B$3:B)}, "select Col2 where Col4 >= "&G2&" and "&G2&" >= Col3"), "")

Sample:

enter image description here

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

user_161186860
Related