IF/AND - reference another table to populate cells

Natasha D

Hi everyone.

I have two sheets. The main sheet has information on a number of assets including columns Replacement Year and Replacement Type. The other sheet is an "indicative cost table" with Replacement Type as the primary column followed by each year 2026, 2027, 2028 etc across the top. The cost of replacement by type, by year has then been populated into the table.

I have created a column in the main sheet called Replacement Cost and I would like to automatically populate this by referencing the Indicative Cost Table. It would need to look at the Replacement Year & the Replacement Type in the main sheet and then pick the correct cost from the array. I am stumped on how to do this other than I expect it would be an index and match formula? Can anyone help please?

Itai Perez

Hey @Natasha D ,

Can you send a screenshot to help me understand the layout of the buid?

Natasha D

Hi @Itai

Thanks for responding. Here is a snip of the main table with the columns I am referring to. The second snip is the indicative cost table. I would like the formula in the 'Replacement Cost' column. It would need to look at the Replacement Type and Replacement Year and then cross reference the data in the second snip. Once it finds the right cell, it will automatically populate the Replacement Cost. Essentially, I only want to update the cost in the indicative cost table instead of manually in the main sheet. Costs change often and it is time consuming and also prone to data errors. I do wonder if this is outside the scope of what Smartsheet can do. Thanks!

Itai Perez

Hey @Natasha D ,

Not sure, worth asking @Andrée Starå

Natasha D

Thanks for having a look @Itai :)

@Andrée Starå do you know if this is possible?

Many thanks

Nick Korna

You should be able to do this with an INDEX/MATCH if your reference sheet is set with Replacement Type next to the costs column wise (or you put a helper column in to duplicate it):

=INDEX({Replacement Type-final year}, MATCH([Replacement Type]@row, {Replacement Type}), (Year@row - 2021))

The 2021 part of the last column can be changed if there are more columns (e.g. including if 2023 was the first year then it would be 2020), and from your initial data you'd get an #INVALID COLUMN VALUE for the third row as is. As your Replacement date/year are calculated from start year + useful lifespan, this could be modified to not display dates in the past (using a MAX formula with current formula as the first value and the 1st available replacement year as the second) but only current/future ones.

Another addition would be to put the above formula inside an IFERROR, but this won't prevent overdue replacements from generating the error mentioned previously, but can display "not known" or blank values if you don't have the data for an upcoming year.

Hope this helps, but if you have any problems/questions, then just post. 🙂

Jen Castillo

Hi! I saw this hasn't been answered so I thought I'd take a stab at it. :)

How manual is your creation of the indicative cost table?

Since we can't reference Column Names in our formulas, I changed made a few changes to the cost table's set up. Replacement Type/Replacement Years are now drop down columns. However, if you have hundreds of Assets/Years, this can be tedious. Although if you're already doing it manually in your cost table, then perhaps this would work? Maybe someone else has a better idea of how to structure it!

Indicative Cost Table (Color-coded for clarity.)

Main Table

Do you reference all the other columns, or just your Replacement Type and Year? I plugged this formula into Replacement Cost while referencing the Cost/Type/Year columns from the cost table. If either Type or Year is missing, I added IFERROR to populate "Missing Data."

=IFERROR(INDEX(COLLECT({Cost}, {Type}, [Replacement Type]@row, {Year}, [Replacement Year]@row), 1), "Missing Data")

Hope this brings you a step closer to what you need!

Natasha D

Thank you @Jen Castillo & @Nick Korna. I have been swapped with something else but will attempt your suggestions next week. I'm sure I'll have questions! :)