Index Match across two sheets giving unexpected results

Hi,

I'm having trouble figuring out what I'm doing wrong for an index match function. I've provided two dummy sheets so you can play with the issue yourself.

The issue (I think) is in "Issues here (dummy).xlsx" column F. The formula I currently have in that column is:
{=IF(E7="","",(IF(E7="source1", INDEX('[Master New Hire List (dummy).xlsx]Sheet2'!$H:$J,MATCH(1,('[Master New Hire List (dummy).xlsx]Sheet2'!$J:$J=C8)*('[Master New Hire List (dummy).xlsx]Sheet2'!$I:$I=B8),0),1),"Issues Here (dummy)")))}

What that column is supposed to be doing is:

1. Given that there is a known match for the first and last name of this row was found in the Master Hire list (indicated by nonblank E7), then
   For all source1→If there is an X in "Master New Hire List (dummy).xlsx" Column H, display an X, if there is not, throw a NA()
   For all Issues Here (dummy)→display Issues Here (dummy)

The goal of this is to roughly help me verify that a name listed in "Master New Hire List (dummy).xlsx" under source1, which also appears in "Issues Here (dummy).xlsx", is denoted in "Master New Hire List (dummy).xlsx" column H by an X.

What it's doing instead:

• Sometimes working
• Throwing a NA even when an X appears in "Master New Hire List (dummy).xlsx" column H
• evaluating to 0 somehow

Ex. where it's giving an unexpected result:

• Issues here (dummy) row 67 & Master New Hire List (dummy) row 104
  • gives a zero for some reason

Thanks .

Can you list at least a few specific cells in column-F that are giving incorrect answers, along with your expected results for those cells.

One observation: Your formula for F7 is referencing B8 and C8. Is that correct or should it be B7 and C7 ??

Administrative Note:

We would very much like to help you with your query, however the thread title does not really convey what your request is about.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

Hi sorry,

Is that a better title?

It will do, thanks.