Can IF(INDEX(MATCH())) return true for multiple rows?

Genevieve P.

Ah yes! My apologies, I read that wrong and missed that the first few rows should be checked. You're correct!

Since the COUNTIFS worked, it seems like it's unable to compare the numbers in the Start and End correctly.

Can we test just with those parameters?

=COUNTIFS({Start Helper}, @cell < [End Helper]@row, {End Helper}, @cell > [Start Helper]@row)

Colin B

Hi Genevieve, thanks for joining in to help. Here's what I got with that formula in the "test" column:

Paul Newcome

So the numbers are working as expected and the dates are definitely dates. Lets make sure the dates are actually working for comparing to each other.

What does this do in your test column?

=IF(COUNTIFS({Date Column}, @cell = Date@row)> 0, 1)

Colin B

Here's the results of that formula:

Genevieve P.

Ah! Thank you for this, @Colin B

Paul may jump in at the same time to ask the same question, but have we already double checked that the Date column in this sheet is actually a Date?

It's unable to find a match between what's in the cell of this sheet and the other sheet. How are you getting the date in this sheet?

Colin B

Yep, both date columns are of date type & restricted to dates only. The dates in this sheet (Timeslot/Engineer Availability) I manually entered, and the dates in the sheet I'm referencing (Appointment Booking w/ Form) are filled in with a formula that takes values from a text string and converts it to a date.

Paul Newcome

@Genevieve P. For reference, this is the formula that populates the date. It is using the DATE function, so I would imagine it is in fact outputting a date type value.

=DATE(VALUE(MID([Selected Timeslot]@row, 7, 2)), VALUE(LEFT([Selected Timeslot]@row, 2)), VALUE(MID([Selected Timeslot]@row, 4, 2)))

And as I type that out... I wonder if we need to adjust this formula. We are only grabbing two numbers for the year portion, so it could be storing as 19## instead of 20##. I didn't look too closely at it because I saw the DATE function and just went from there.

@Colin B Try making this adjustment for the formula stripping out the date and then see what happens:

=DATE(VALUE("20" + MID([Selected Timeslot]@row, 7, 2)), VALUE(LEFT([Selected Timeslot]@row, 2)), VALUE(MID([Selected Timeslot]@row, 4, 2)))

Genevieve P.

If you've just solved it IOU a chocolate bar. 1923! 😂 It may have even been looking for the year 0023!!! I totally missed that as well.

Genevieve P.

I just tested with the original formula and that's exactly what was happening:

Paul Newcome

@Genevieve P. I usually try to build that in when I am creating a formula like that, but I didn't think to check for it initially. I was just checking to see if we had a date. I didn't even think of it when we got the results of looking for a date match. It wasn't until I was pasting the formula into my last comment to show you we had dates that I realized the MID function for the year was only pulling 2 characters. Haha.

Colin B

That was it!

Now I just have to address it returning true for multiple engineers, but I think I can handle that on my own by also checking for names in the Booked formula or by creating a separate sheet for each engineer.

Thank you both so much for your help @Paul Newcome and @Genevieve P. !!!

Genevieve P.

Woohoo! Well-done, and thanks for sticking with us!

Paul Newcome

Awesome. Happy to help. 👍️