Excel Formula for matching two columns and referencing a third column

[excel_safely]

Hi there, 

I'm wanting an excel formula that looks at two columns on different spreadsheets and where there is a match, references a different column value as per below: 

Tab 1

CARDHOLDER (tab/table title) 

Column A: FullName (column title) 

Tab 2 

CONTACTS (tab/table title) 

Column J: Combined (column title) 

Where these two match then the value shown in this adjacent row in tab 2 will appear: 

CONTACTS (tab/table title) 

Column C: Role (column title) 

Ideally this would all appear on a different tab to allow a pivot table to be neatly run. 

I have been playing with a INDEX / MATCH formula but haven't managed to get it working

=IFERROR(INDEX(Table2[[#Headers],[Role]], MATCH(1, INDEX(($A8=CARDHOLDER[FullName])*(Contacts[[#Headers],[Combined]]),0, 1), 0))," ")

[#2 · Accepted solution]

@excel_safely 

Please try this formula:

=INDEX(CONTACTS[Role],MATCH(CONTACTS[Combined],CARDHOLDER[FullName],0))

Alternatively with the more modern XLOOKUP() function:

=XLOOKUP(CONTACTS[Combined],CARDHOLDER[FullName],CONTACTS[Role])