Nested INDEX/MATCH formula for conditional cross-sheet reference use case

Victoria Anzalone

I am currently using the following INDEX/MATCH formula to return values from the sheet labeled "Tech Project List" in the reference.

=INDEX({Summary from Tech Project List}, MATCH([Tech List Row ID]@row, {Project ID from Tech Project List}, 0))

I now want to expand that formula to include a condition: If #NO MATCH on Tech Project List, then do the same INDEX/MATCH formula but instead reference "Tech Cancelled List."

What would that formula structure look like?

This may be obvious but for context: I am mainly sourcing my information from Tech Project List, the sheet where our proposed/active Tech PMO projects live. The problem is when project rows change to Cancelled status, the Tech Project List has an automation to send those rows I was referencing with my formula to the Cancelled List sheet.

Georgie

Hi @Victoria Anzalone,

I’d use the IFERROR Function to do this - so, when the formula would return an error because there’s not a match in the Tech Project List sheet, we can tell it to index the Tech Cancelled List sheet instead, by providing the alternate INDEX(MATCH) formula within the IFERROR formula. So, where your existing formula is:

• =INDEX({Summary from Tech Project List}, MATCH([Tech List Row ID]@row, {Project ID from Tech Project List}, 0))

We can change it to:

• =IFERROR(INDEX({Summary from Tech Project List}, MATCH([Tech List Row ID]@row, {Project ID from Tech Project List}, 0)), INDEX({Summary from Tech Cancelled List}, MATCH([Tech List Row ID]@row, {Project ID from Tech Project List}, 0)))

Essentially, we’ve repeated your INDEX(MATCH) formula again but with a different INDEX - that is, the {Summary from Tech Cancelled List} reference will be the column you want to return the value from on your Tech Cancelled List sheet. So, the formula will first check the Tech Project List sheet to see if the Row ID has a match - if it does, it’ll return the value from the Summary from the Tech Project List range, and if it doesn’t, it’ll check the Tech Cancelled List sheet and return the value from there instead.

Hope that helps!

Georgie

Victoria Anzalone

Exactly what I was looking for. Thank you!

Georgie

Hi @Victoria Anzalone,

I’d use the IFERROR Function to do this - so, when the formula would return an error because there’s not a match in the Tech Project List sheet, we can tell it to index the Tech Cancelled List sheet instead, by providing the alternate INDEX(MATCH) formula within the IFERROR formula. So, where your existing formula is:

• =INDEX({Summary from Tech Project List}, MATCH([Tech List Row ID]@row, {Project ID from Tech Project List}, 0))

We can change it to:

• =IFERROR(INDEX({Summary from Tech Project List}, MATCH([Tech List Row ID]@row, {Project ID from Tech Project List}, 0)), INDEX({Summary from Tech Cancelled List}, MATCH([Tech List Row ID]@row, {Project ID from Tech Project List}, 0)))

Essentially, we’ve repeated your INDEX(MATCH) formula again but with a different INDEX - that is, the {Summary from Tech Cancelled List} reference will be the column you want to return the value from on your Tech Cancelled List sheet. So, the formula will first check the Tech Project List sheet to see if the Row ID has a match - if it does, it’ll return the value from the Summary from the Tech Project List range, and if it doesn’t, it’ll check the Tech Cancelled List sheet and return the value from there instead.

Hope that helps!

Georgie

Victoria Anzalone

Exactly what I was looking for. Thank you!