Help with complex Google Sheets formula for conditional date display

Other Questions Google Sheets
1

I’m working on a spreadsheet and need help with a tricky formula. Here’s what I want to do:

  1. Compare column G with column A (A2-A17 and A21-A33 separately)
  2. Check if column B is marked as ‘True’ for each section
  3. If it’s true, return the date (from A1 or A20) in column H next to the matching Stop
  4. If it’s false, leave the cell empty

I’ve tried combining different formulas but can’t get it to work right. Any ideas on how to make this happen? I’m pretty new to advanced formulas so any tips would be great!

Here’s a quick example of what my sheet looks like:

Date Checked Stop
5/1 TRUE Park
5/1 FALSE Mall
5/2 TRUE Bank

Thanks in advance for any help!

2

hey mate, i think i got a solution for ya. try this formula:

=IF(AND(B2=TRUE,OR(MATCH(G2,$A$2:$A$17,0),MATCH(G2,$A$21:$A$33,0))),IF(MATCH(G2,$A$2:$A$17,0),$A$1,$A$20),“”)

it should do what u want. just copy it down column H. lemme know if u need more help!

3

I’ve been in a similar situation before, and I can tell you it’s definitely tricky to get right. After some trial and error, I found a solution that might work for you:

=ARRAYFORMULA(IF(B2:B=TRUE, IF(MATCH(G2:G, {A2:A17; A21:A33}, 0), IF(ROW(G2:G)<=17, A1, A20), “”), “”))

This formula uses ARRAYFORMULA to apply the logic to the entire column at once. It checks if the ‘Checked’ column is TRUE, then looks for a match in both ranges you specified. If found, it returns the appropriate date based on which range matched.

One thing to watch out for: make sure your data is consistently formatted, especially the dates. I learned that the hard way when my formulas kept returning weird results.

Give it a shot and see if it works for your sheet. If you run into any issues, let me know and I’ll try to help troubleshoot.

4

This is a challenging task, but I think I can help you out. You’ll want to use a combination of INDEX, MATCH, and IF functions to achieve what you’re looking for. Here’s a formula that should work:

=IF(AND(B2=TRUE, MATCH(G2, $A$2:$A$17, 0)), $A$1, IF(AND(B2=TRUE, MATCH(G2, $A$21:$A$33, 0)), $A$20, “”))

This formula checks if B2 is TRUE and if G2 matches any value in A2:A17 or A21:A33. If there’s a match, it returns the corresponding date from A1 or A20. Otherwise, it leaves the cell empty.

You’ll need to adjust the cell references based on your actual sheet layout. Also, make sure to drag this formula down column H to apply it to all rows.

Hope this helps! Let me know if you need any clarification on how the formula works.