IF statement using INDEX/COLLECT

Hello!

I am looking to write an IF statement referencing 2 different sheets saying "IF the value is not found on sheet 1, look for it on sheet 2."

I am also looking to pull in the most recent entry on my reference sheets.

=IF(INDEX(COLLECT({range 1, SLCA}, {criteria 1 SLCA, equipment number}, [equipment number]@row), 1), INDEX(COLLECT({range 1, SLCA}, {criteria 1 SLCA, equipment number}, [equipment number]@row), 1), INDEX(COLLECT({range 1, HSTX}, {criteria 1 HSTX, equipment number}, [equipment number]@row), 1))

Each INDEX/COLLECT statement returns the proper values when written on their own. As soon as I write the IF statement, I get an #INVALID DATA TYPE. The column on the master sheet is a text/number, and the reference columns are also text/number. Looking for suggestions!

Thank you!

Find more posts tagged with

Accepted answers

All comments

Cory Page

Hello, couple of quick questions.

is the most "recent" data at the top of your sheet or the bottom? I know web forms allow for top submission which makes your second need a lot easier.
As for a quick formula you could do index/match with an iferror in front.
"Example: "=IFERROR(INDEX([If you want the count on every row]12:[If you want the count on every row]19, MATCH("Bob", [Column13]12:[Column13]19, 0)), INDEX([If you want the count on every row]12:[If you want the count on every row]19, MATCH("Bob", [Column13]12:[Column13]19, 0)))

Index/match will find the first example and bring it back, this would solve both of your needs if the new data flows into the top of the sheet. Figured we could start with this real fast.

If you would like better instructions please submit a screenshot of some sample data and columns and I can make a quick copy.

Carlee Schiffner

Hi Cory!

The most recent data is at the top of the sheet.

I used the index/match wrapped in the iferror and it worked!

Thank you

Cory Page

@Carlee Schiffner Perfect, glad it worked for you.. Hope you have a great day..

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.