Forum Discussion

excel_safely's avatar
excel_safely
Copper Contributor
Nov 17, 2022
Solved

Excel Formula for matching two columns and referencing a third column

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))," ")

excel
Formulas and Functions
  • dscheikey's avatar
    dscheikey
    Nov 19, 2022

    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])

     

1 Reply

  • dscheikey's avatar
    dscheikey
    Bronze Contributor
    Nov 19, 2022

    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])

     

Resources