Using either INDEX MATCH MATCH or INDEX COLLECT

I am trying to setup a single row sheet with a search function that will return a value "Alarm Info" from a reference sheet that contains a large dataset. I need to search the data by 2 variables (Tool and Alarm #) that are set on the single row sheet. I have tried:

=INDEX({Master Alarm Sheet Range 1}, MATCH(Tool1, {Master Alarm Sheet Range 3}, 0), MATCH([Enter Alarm No. (Example 3931)]1, 0), 2)

and

=INDEX(COLLECT({Master Alarm Sheet Range 1}, Tool1, {Master Alarm Sheet Range 3}, [Enter Alarm No. (Example 3931)]1, {Master Alarm Sheet Range 2}), 1)

Initially I had several alarm sheets, one dedicated to each tool but I couldn't nest more than 2 INDEX MATCH in an IF statement, so I combined all of the sheets into one.

Quickly loosing my mind on this one.....

Find more posts tagged with

Accepted answers

All comments

Kelly Moore

Hey @Superhawk13

A Collect has the syntax of COLLECT({range you want the data from}, {range 1}, criteria 1, {range 2}, criteria 2, etc)

I think you have the range-criteria order reversed.

I can only guess that the ranges are the ones related to your criteria. A good practice when using cross sheet references is to change the name of the generically names ranges to the name of the specific column you are referencing. This will help you and others troubleshoot your formulas.

=INDEX(COLLECT({Master Alarm Sheet Range 1}, {Master Alarm Sheet Range 3}, Tool@row, {Master Alarm Sheet Range 2}, [Enter Alarm No. (Example 3931)]@row), 1)

Does this work for you?

Kelly

Superhawk13

Kelly,

Thank you for the help. I still get an INCORRECT ARGUEMENT SET with your example. I was using the entire range of Master Alarm Sheet. This may be part of my error, in the below example I only selected the column with the text I am trying to return as the range. This returns an INVALID VALUE.

=INDEX(COLLECT({Master Alarm Sheet Alarm Text}, {Master Alarm Sheet Tool}, Tool@row, {Master Alarm Sheet Alarm Code}, [Enter Alarm No. (Example 3931)]@row), 1)

Kelly Moore

Hey

Are there multiple text comments that would be associated with the Tool-Code combination? That its, are you expecting any/all comments to be collected that fit that criteria, or is there a unique text that fits the criteria?

Also, to be clear, all ranges now refer to a single column in your source sheet?

In troubleshooting here's what I try:

Verify the target sheet column names (your @rows) show up in colored text.

Delete a single range-criteria pair from the formula and see if the formula works without it. I do this until I get a pair to work. So remove the Alarm code range and the [Enter Alarm No....] from your formula. Does it work?

Also, double check your ranges. Completely delete them, one by one, from your formula and go back into the Insert Reference window and re-insert them, making sure the correct column was originally selected.

Try these, one by one, and let me know what happens.

Superhawk13

Shoot, the last setup worked. I had inadvertently entered an alarm number that did not exist in the list. Go figure out of almost 20,000 alarms that number was not assigned.....

Many thanks good Sir.

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.