Using two index/match formulas to sync data from 2 sheets to a master

Caroline Elliott

I'm trying to pull data from two different sheets into a master sheet using two index/match formula's within an iferror however it is only partially working with some pulling through correctly and others coming up as no match even though there is a match. In the example below KYP-RSK-002 should bring a review status of Due through to the master from the strategic risk register which is working correctly for KYP-RSK-0001.

Any help would be very welcome!

Here's the formula I'm using:

=IFERROR(INDEX({Strategic Risk Register Review Status}, MATCH([Risk ID]@row, {Strategic Risk Register Risk ID}, 0)), INDEX({Operational Risk Register Review Status}, MATCH([Risk ID]@row, {Operational Risk Register Risk ID}, 0)))

Columns in Master:

Columns in Strategic Risk Register:

Columns in Operational Risk Register:

jmyzk_cloudsmart_jp

Hi @Caroline Elliott

It worked fine when I tested your formula in the demo solution below.

A possible cause would be your  {Strategic Risk Register Risk ID} range does not cover the whole column.

My demo solution considers the possibility of having both Strategic and Operational risk for the same task or project. So, I used the IFERROR for each MATCH function. If the MATCH function returns a row number, which is a number, the formula retrieves the respective Review Status.

For example, 003 returns "S: Reviewed O: Reviewed."

https://app.smartsheet.com/b/publish?EQBCT=c97b916f18e8420981fc66dae130957a