INDEX COLLECT with criteria matching in one column OR another

Annie Prescott

I have an equipment PM submission log that receives data from an electronic form and feeds "Date Performed" into the corresponding cell on a master spreadsheet using Index Collect when the Asset Name, Asset # and PM Type match. I have now been asked to make the formula work when the Asset #/Serial # column on the submission log matches EITHER the Asset # column OR the Serial # column on the master spreadsheet.

1st image is the Submission Log where users select asset #s OR Serial #s from a single drop down . 2nd image is the master spreadsheet with the 2 columns that contains this data for each piece of equipment. Some assets do not have serial #s and vice versa, so I need a formula that will work for either column if a match is found with the data in the "Asset/Serial#" column. I'm open to using helper columns or complex formulas if it prevents me from changing what an end user has to input on the electronic form.

Danielle Arteaga

Hi, Annie. I'm not sure I completely follow what you want here, but I took a stab at it.

I'm going to call your first screenshot "Equipment List" and your second screenshot "Asset List."

On the Asset List, I added two new columns: "Asset Match" and "Serial Match"

In the Asset Match column, I entered this formula:

=IFERROR(INDEX({Equipment List Date Performed}, MATCH([Asset #]@row, {Equipment List Serial}, 0)), "")

The reference {Equipment List Date Performed} corresponds to the "Date Performed" column. Please make sure this is formatted as a "date" column type.

In the Serial Match column, I entered this formula:

=IFERROR(INDEX({Equipment List Date Performed}, MATCH([Serial #]@row, {Equipment List Serial}, 0)), "")

YOU CAN HIDE BOTH OF THESE COLUMNS. They are basically now "helper" columns that will populate your date fields to the right.

In the "Jan" column, I entered this formula:

=IFERROR(IF(MONTH([Serial Match]@row) = 1, [Serial Match]@row, IF(MONTH([Asset Match]@row) = 1, [Asset Match]@row, "")), "")

This tells Smartsheet to copy the date from the "Serial Match" column if the month in the date is January. If the Serial Match column does not resolve true, then it tells Smartsheet to check copy the date from the "Asset Match" column if the date is January. If neither resolves true, it tells Smartsheet to leave the field blank.

I then copied this same formula into the February and March columns by changing the "1" to a "2" or a "3" as corresponds with the month value.

Here's what the resulting sheet looks like with the Asset Match and Serial Match columns hidden:

The red text indicates a value I made up for testing.

Hopefully this works for you. Please post a follow-up if I've misunderstood something. Happy to try again for you.