Using two index/match formulas to sync data from 2 sheets to a master
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:
Answers
-
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."
-
Thank you for the response. I checked the range and it looks ok. It also worked for me when I did a POC on a smaller number of rows. Maybe I've hit a limit somehow. It's a mystery!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!