Please help with INDEX(COLLECT() FORMULA

Nikki_85

Hello, I am trying to use INDEX & COLLECT instead of VLOOKUP as I want to return a value from another sheet based on two criteria.

I want to lookup a column containing Building ID and find it in the source sheet, returning who has completed the check. I also want to check the date completed was within a date range of -1 & +3 days of the due date, for which I have added a 'helper column' to show if the check was completed on time. The helper column is in the same sheet as the formula.

The formula I have tried is:

=INDEX(COLLECT({Completed By}, [Call Point Building &ID]@row, {Helper}, [Completed On Time]@row, =1))

The response I am getting is #UNPARSEABLE

Please can someone let me know where I am going wrong?

Matt Lynn-PCG

@Nikki_85 One thing is you have your reference and criteria backwards. Fix this and then see if the rest works. I'm unsure about your date part… maybe a screenshot of what you have would help. Also your second criteria is off. "[Completed on Time]@row, =1??

The reference here is looking for a range, not a @row…. and you don't need the = sign here. However once you change the @row to a reference you may need a @cell = 1 for the criteria. Again.. .a screenshot would help.

Paul Newcome

Try something along the lines of…

=INDEX(COLLECT({Completed By}, {Building ID Range}, @cell = [Call Point Building &ID]@row, {Helper}, @cell =1), 1)

Nikki_85

Thank you both for your advice :)