Running INDEX / MATCH formula only when certain criteria are met

Emma Picardi

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

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?