Index Collect Formula (i think) question

I am trying to return a value from another sheet based on 2 pieces of criteria. I am looking to return the "Date Requested" data based on the "Operator" and "Request Type" criteria. This is the formula I am using. I am getting an INCORRECT ARGUMENT error however. Appreciate any guidance

=INDEX(COLLECT({Operator}, Operator@row, {Request Type}, [Request Type]@row, {Date Requested}, 0))

Find more posts tagged with

Accepted answers

All comments

Ryan Doyle

I've updated my formula to this, and now getting an INVALID VALUE error

=INDEX(COLLECT({Date Requested}, {Operator}, Operator@row, {Request Type}, [Request Type]@row), 1)

Kelly Moore

Hey @Ryan Doyle

Is the column type where your formula sits a DATE column? Your COLLECT data appears to be a date value.

Kelly

Ryan Doyle

Hi @Kelly Moore

Yes, the column type where my formula sits is a DATE column.

Kelly Moore

@Ryan Doyle

Is it possible for screenshots of your source and destination sheets? As a test you can run, first delete {Request Type}, [Request Type]@row from the formula. Do you still get an error? If not, put the Request type range-criteria back in the formula and take the Operator range-criteria out of the formula. This will tell you what criteria is a problem.

Kelly

Humashankar

Good analysis

Ryan Doyle

@Kelly Moore

I was able to solve my problem. Ultimately I had the formula correct. I had mismatching values between the source sheet and the destination sheet. I was so focused on the complexity of the formula that i overlooked the easy part.

thank you so much for helping!

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.