Why do I get #invalid value?

Hi everyone!

I am using this formula in the Date column to pull a date if 2 criteria are met (name of the client and the specific module): =INDEX({Date}, MATCH([Column11]3, {Client}, 0), MATCH([Column4]@row, {Module}, 0))

It works in the "Module 1" row, but not below, any idea why? Thanks!

Find more posts tagged with

Formulas

Accepted answers

Paul Newcome

You need to specify which item from the list that the COLLECT function pulls together that you want to INDEX. Try putting a comma one in between the two closing parenthesis at the end.

=INDEX(COLLECT(.................), 1)

All comments

ker9

@anaMG

See this post

https://community.smartsheet.com/discussion/86176/index-match-using-multiple-criteria

anaMG

Thank you, I tried INDEX(COLLECT, but I get "Incorrect argument set".

This is the formula I tried: =INDEX(COLLECT({Date}, {Client}, [Column11]3, {Module}, [Column4]@row))

Paul Newcome

You need to specify which item from the list that the COLLECT function pulls together that you want to INDEX. Try putting a comma one in between the two closing parenthesis at the end.

=INDEX(COLLECT(.................), 1)

anaMG

Thank you Paul! That did it, I don't really understand why, but it worked!

Thanks!

Paul Newcome

Basically the COLLECT pulls together an array or list from the first range based on the rest of the range/criteria sets.

Then we use the INDEX function to pull from that list. An array built on the back-end such as one built by the COLLECT function will always be in a vertical format, so we use a one in the "row to pull from" section of the INDEX function to tell it to pull the first value from the array.

Chris Clem

Hey Paul, I am having a similar issue with my build. It will populate a Contact in one search but when I change search criteria it will give me a #INVALID VALUE. I am using a INDEX COLLECT formula to search another sheet and match certain columns on the main sheet. So, have a collector or Data sheet in backround.

Here is the formula:

=(INDEX({Grounds Maintenance Exhibit Assignment Range 2}, MATCH(Exhibit@row , {Exhibit}, 0), MATCH([Field Report Type]@row , {Grounds Maintenance Exhibit Assignment Range 3}, 0)))

When I change the "Field Report Type", it will give the error. Otherwise, it works and returns the Contact to the row I am looking for.

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.