Index match based on two tables

[NathanA]

I am using an individual number (Master NSN) on my main sheet to return Team name based on two other tables. I match the tims-nsnnumber from the first table (Achieved10Credits) with the Master NSN.

The main issue lies with adding in a second criteria based on another lookup. I aim to use the Master NSN to match information with the first table (Achieved10Credits) which contains fullname to return OwnerName from the second table (Lookup). However the formula I am using at the moment returns #NA where I don't expect a result, and #REF ! where I do.

Any guidance would be much appreciated.

 =INDEX(Lookup[Team name],MATCH([@[Master NSN]],Achieved10Credits[tims_nsnnumber],0),MATCH(Achieved10Credits[fullname],Lookup[OwnerName],0))

entered as an array formula

 

[jarjarbingie]

I am using an individual number (Master NSN) on my main sheet to return Team name based on two other tables. I match the tims-nsnnumber from the first table (Achieved10Credits) with the Master NSN.

The main issue lies with adding in a second criteria based on another lookup. I aim to use the Master NSN to match information with the first table (Achieved10Credits) which contains fullname to return OwnerName from the second table (Lookup). However the formula I am using at the moment returns #NA where I don't expect a result, and [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=REF]#REF [/URL] ! where I do.

Any guidance would be much appreciated.

 =INDEX(Lookup[Team name],MATCH([@[Master NSN]],Achieved10Credits[tims_nsnnumber],0),MATCH(Achieved10Credits[fullname],Lookup[OwnerName],0))

entered as an array formula

first, it doesn't have to be entered as an array formula

second, it returns a #REF ! error since the Lookup[Team name] is just one column but you have parameters for both row_num and col_num, for example the first MATCH function (row_num) returns 3 and the second MATCH (col_num) returns 5, therefore, it would look into Lookup[Team name], go 3 rows down and 5 columns to the right, but since the array is just one column wide, it would give #REF ! error