Running INDEX / MATCH formula only when certain criteria are met

Hi there,

This title might be misleading, but couldn't find a better way to summarize. Apologies!

I'm trying to use an INDEX / MATCH formula to pull in data from a secondary sheet when the search value in my main sheet matches a cell in the secondary one, but if no match is found, leave the existing cell text as is. I already have the INDEX / MATCH piece working for another set of ranges populating equipment serial numbers from an input sheet into our main data collection sheet, and that looks like this:

=INDEX({Serial #, MATCH([Fleet No.]@row, {Fleet No.}, 0))

I'd like to use this formula a second time in a column that already has input cell values, and I only want those values to be updated per the INDEX / MATCH search IF a match is found in the secondary sheet. Is this possible? And if so, is it as simple as adding an IF()?

Thanks!

Emma

Find more posts tagged with

Accepted answers

All comments

KT_H

I think you are looking for INDEX/COLLECT, this will only pull in the data if criteria are met as detailed in the COLLECT portion of the formula.

The syntax is slightly different than index match...

For example:

=INDEX(COLLECT({range to pull value from}, {criterion range}, criteria... etc))

Give it a try with your criteria and let me know if any issues pop up!

Emma Picardi

Thanks KT! I'm realizing that what I'm trying to do probably isn't possible within a single column since once I replace the existing text with a formula, there obviously won't be any text to not overwrite if the formula doesn't return a matching value. So, maybe I need a secondary column to work with? And/or some kind of automation function?

Basically, the column in our master sheet with existing text entries uses a drop down menu from which we select a set of equipment specs. We're in the process of replacing some of this equipment, however, and when those replacements happen, we have our operations team fill out a form that populates a different sheet noting what the new equipment specs are. Ideally, I'd like the new specs in the form-populated sheet to replace the text in the equipment spec column in our master sheet. Maybe the simplest thing to do is just to add a second column for "New Equipment" that uses INDEX / MATCH to pull in the data from the form-populated sheet. And from there... I'm not sure if there's an automated way to marry up those two columns. What do you think?

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.