IFERROR, MATCH, INDEX functions to find differences of two sheets

The problem is you are using & signs incorrectly. What that translates to is:

Using the highlighted row:


=IFERROR(INDEX(BC!$B$2:$B$9480,MATCH(B8&C8,INDEX(BC!$C$2:$C$9480&BC!$D$2:$D$9480,0),0)),"Not Found")

=IFERROR(INDEX(BC!$B$2:$B$9480,MATCH(D123-MMD-C4-EFA-C101_6-00001C04,INDEX(BC!$C$2:$C$9480&BC!$D$2:$D$9480,0),0)),"Not Found")

That full string isn't found on BC anywhere

I will edit this post with a solution in a moment, I just wanted to point that out first, maybe you can figure it out based on that before I do

thank for pointing this out. I might need to check my spreadsheets now.

But in this case, because the file D123-MMD-C4-EFA-C101_6-00001 is on the list of BC, but rev P22 and not rev C04,
I want to not only see 'Not Found' (what I have now), but also what revision BC has - in this case P22.

And if it is not on the list, then show 'Not Found'. If possible.. Is it possible?.. )

But you might be right about & signs, I am a beginner with those :D

May be this......

On Sheet1, in D2

=IFERROR(INDEX(BC!$B$2:$B$9480,MATCH(B2&C2,INDEX(BC!$C$2:$C$9480&BC!$D$2:$D$9480,0),0)),IF(ISNUMBER(MATCH(B2,BC!$C$2:$C$8286,0)),INDEX(BC!$D$2:$D$8286,MATCH(B2,BC!$C$2:$C$8286,0))&" Not Found","Not Found"))

Just remove first "Not Found" from the formula like this......

=IFERROR(INDEX(BC!$B$2:$B$9480,MATCH(B2&C2,INDEX(BC!$C$2:$C$9480&BC!$D$2:$D$9480,0),0)),IF(ISNUMBER(MATCH(B2,BC!$C$2:$C$8286,0)),INDEX(BC!$D$2:$D$8286,MATCH(B2,BC!$C$2:$C$8286,0)),"Not Found"))

You're welcome and thanks for the feedback.
If that takes care of your question, please mark your thread as Solved by selecting Thread Tools (just above your first post) --> Mark thread as solved.