Filling cells based on multiple criteria in Google Sheets?

Other Questions Google Sheets
1

I’m working on a project in Google Sheets and I’m stuck. Here’s what I’m trying to do:

Sheet 1 has:

  • Column A: TV station names
  • Column B: Ad airing times (format: 14-12-22 08:06:05)

Sheet 2 has:

  • Column A: TV station names
  • Column B: Time ranges (format: 09:00-16:00)
  • Column C: Rates

I want to add a Rate column to Sheet 1. The rate should be based on matching the TV station name AND the time falling within the range from Sheet 2.

I thought about using VLOOKUP, but I’m not sure how to handle the time range part. Any suggestions on how to make this work? I’m pretty new to complex formulas in Google Sheets.

Thanks for any help!

2

hey, have you tried using a combo of INDEX and MATCH? it’s pretty neat for this kinda stuff. you could do something like:

=INDEX(Sheet2!C:C, MATCH(1, (Sheet2!A:A=A2)(TIMEVALUE(MID(B2,10,8))>=TIMEVALUE(LEFT(Sheet2!B:B,5)))(TIMEVALUE(MID(B2,10,8))<=TIMEVALUE(RIGHT(Sheet2!B:B,5))), 0))

might need some tweaking but should get u started. good luck!

3

This is a challenging task, but it’s definitely doable with a combination of functions. I’d suggest using a QUERY function wrapped in an ARRAYFORMULA to handle this efficiently. Here’s a formula you could try in the Rate column of Sheet1:

=ARRAYFORMULA(IFERROR(QUERY(Sheet2!A:C, “SELECT C WHERE A = '”&A2&“’ AND “&TIMEVALUE(MID(B2,10,8))&” >= TIMEVALUE(LEFT(B,5)) AND “&TIMEVALUE(MID(B2,10,8))&” <= TIMEVALUE(RIGHT(B,5)) LIMIT 1”, 0), “”))

This formula checks for matching station names and whether the airing time falls within the specified range. It’s a bit complex, so you might need to adjust it based on your exact sheet setup. If you’re not comfortable with such advanced formulas, you could also consider using Apps Script to create a custom function for this task.

4

I’ve tackled a similar problem before, and I found that using INDEX and MATCH functions together can be really effective for this kind of lookup with multiple criteria. Here’s what worked for me:

=INDEX(Sheet2!C:C, MATCH(1, (Sheet2!A:A=A2)(TIMEVALUE(MID(B2,10,8))>=TIMEVALUE(LEFT(Sheet2!B:B,5)))(TIMEVALUE(MID(B2,10,8))<=TIMEVALUE(RIGHT(Sheet2!B:B,5))), 0))

This formula looks up the rate based on matching station names and checks if the airing time falls within the specified range. It’s a bit complex, but it’s more flexible than VLOOKUP for multiple conditions.

You might need to adjust the cell references to fit your sheet. Also, make sure your time formats are consistent across both sheets for this to work properly. If you run into any issues, let me know and I can help troubleshoot.