Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
SOLVED

# Cross reference two columns

Copper Contributor

# Cross reference two columns

Hello

I'm trying to figure out how to first cross reference two text string columns and then filter it so i only have duplicates and then cross reference the duplicates in relation to the vendor number they are given to see if the two vendors have been asigned the same vendor number e.g.

 D.Number D.Name P.Number P.Name 1001 Keith 1234 Peter 1234 Peter 1001 Keith 1002 Ralph 1005 Ralph

I Hope this make sense, I have about 1400 lines that I need to sort xD

3 Replies
best response confirmed by MNymark485 (Copper Contributor)
Solution

# Re: Cross reference two columns

``=INDEX(\$C\$2:\$C\$9,MATCH(B2,\$D\$2:\$D\$9,0))=A2``

You can try this formula. It returns TRUE or FALSE (WAHR or FALSCH in german Excel) if the D.Number and P.Number are the same for the name in column B (D.Name).

# Re: Cross reference two columns

Very nice! Can you help with N/A cells then ? Is that because the value or name is not present in both columns?

# Re: Cross reference two columns

``=INDEX(\$C\$2:\$C\$11,MATCH(B2,\$D\$2:\$D\$11,0))=A2``

In the example the NA (NV in german Excel) occurs because the D.Name doesn't occur in the P.Name column.

``=IFERROR(INDEX(\$C\$2:\$C\$11,MATCH(B2,\$D\$2:\$D\$11,0))=A2,"")``

You can wrap the formula into IFERROR if you want to return e.g. an empty instead of NV.

1 best response

Accepted Solutions
best response confirmed by MNymark485 (Copper Contributor)
Solution

# Re: Cross reference two columns

``=INDEX(\$C\$2:\$C\$9,MATCH(B2,\$D\$2:\$D\$9,0))=A2``

You can try this formula. It returns TRUE or FALSE (WAHR or FALSCH in german Excel) if the D.Number and P.Number are the same for the name in column B (D.Name).