Please help with INDEX(COLLECT() FORMULA

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?

Find more posts tagged with

Accepted answers

All comments

Matt Lynn ACT

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.