Matching Single Accounts and Shared Accounts

[LAB14086]

I have a single spreadsheet with individual rows of data representing one person. They also have a spouse somewhere in the spreadsheet in a separate row.

There are two different kinds of ID numbers in my file. One is a singleID#.  And the other is a shared accountID# (all rows have both) The single ID is specific to the "person", the shared ID by the "household".  

So here's what needs to happen...

Mr. John Doe  |   IndivID:19467   |   SharedID: 12345   |   SpouseID: #####   |   SpouseName: XXXXX

Ms. Jane Doe  |  IndivID: 16493  |   SharedID: 12345   |    SpouseID: #####   |   SpouseName: XXXXX

I must match the two shared accounts based on their common SharedID, then create a new column with the opposite spouses IndivID, then also fill in their spouses name.  Many of the people may not have a related spouse account so I assume they will be blank.

I've been trying VLOOKUP and Index & Match but I'm absolutely stuck. I either returns me their own indivID, or it will return the first spouse's ID listed based on it's placement (above/below) the others.

[#2]

@LAB14086 

Assuming that IndivID is in column B and SharedID in column C, starting in row 2, enter the following formula in the first available column in row 2:

=IFERROR(INDEX($B$2:$B$101,MATCH(1,($C$2:$C$101=C2)*($B$2:$B$101<>B2),0)),"")

Modify the ranges if you have more than 100 rows of data.

If you don't have Microsoft 365 or Office 2021, confirm the formula with Ctrl+Shift+Enter.

Then fill or copy down.