Match Two Columns from 2 Tabs to Return Info from 3rd

[Xenophile]

Hello! I am trying to figure out a way to match 2 columns (location and ID) from one sheet to 2 columns on a second sheet (location and ID) to return information from a 3rd column on the second sheet (statement type). My issue is that the ID may be the same but the location could change (pt 123 at office 1 is different from pt 123 at office 2) but I need a master list of the statement types. Any guidance would be appreciated! Thank you very much!

[#2 · Accepted solution]

@Xenophile 

To match two columns (location and ID) from one sheet to two columns (location and ID) on a second sheet and retrieve information from a third column (statement type), you can use a combination of the INDEX and MATCH functions in Excel.

Here's a step-by-step guide:

Assuming:

• Sheet1 contains the data you want to match (with location in column A, ID in column B, and other data in column C).
• Sheet2 contains the master list of statement types (with location in column A, ID in column B, and statement type in column C).
• You want to retrieve the statement type from Sheet2 and display it in Sheet1.

1. In Sheet1, in the column where you want to display the statement type (let's say column D), enter the following formula in the first cell (e.g., D2):

=INDEX(Sheet2!$C$2:$C$100, MATCH(A2&B2, Sheet2!$A$2:$A$100&Sheet2!$B$2:$B$100, 0))

Make sure to adjust the range references (Sheet2!$C$2:$C$100, Sheet2!$A$2:$A$100, and Sheet2!$B$2:$B$100) to match the actual range containing the statement types and the corresponding location and ID on Sheet2.

1. Press Enter to apply the formula to the first cell, and then copy the formula down to the rest of the cells in column D to populate the statement types for each corresponding location and ID.

The formula uses the INDEX and MATCH functions together:

• The MATCH function searches for a combination of location and ID (A2&B2) in the range of location and ID columns (Sheet2!$A$2:$A$100&Sheet2!$B$2:$B$100) on Sheet2. The third argument, 0, specifies an exact match.
• The INDEX function retrieves the corresponding statement type (Sheet2!$C$2:$C$100) based on the position of the match.

This approach allows you to match the two columns and retrieve the statement type even if the location may change for the same ID.

[#3]

@NikolinoDE, I cannot thank you enough! I've been working on this for a couple of days and I felt like the index match was the way to go but for the life of me I couldn't get it to make sense or work. I so appreciate your explanation! It worked! Thank you so much!

[#4]

@NikolinoDE 

how can we do this in sharepoint file

[#5]

@kanth_12 

In SharePoint Online, you can achieve a similar functionality by utilizing calculated columns and lookup columns within a SharePoint list. Here's how you can set it up:

Assuming you have two lists in SharePoint:

1. List 1: Contains the data you want to match, including the location, ID, and any other relevant information.
2. List 2: Contains the master list of statement types, including the location, ID, and statement type.

Follow these steps:

1. Add a Lookup Column to List 1:

• Go to List 1 in SharePoint.
• Click on "Settings" (gear icon) and select "List settings."
• Under "Columns," click on "Create column."
• Choose "Lookup" as the column type.
• Set the "Get information from" field to List 2.
• Select the columns "Location" and "ID" from List 2 to match with.
• Choose the column "Statement Type" from List 2 to retrieve data from.
• Save the column.

2. Create a Calculated Column to Concatenate Location and ID:

• In List 1 settings, under "Columns," click on "Create column."
• Choose "Calculated" as the column type.
• Enter a name for the column (e.g., "LocationID").
• In the formula box, concatenate the location and ID columns.

For example: =[Location]&"-"&[ID]

• Save the column.

3. Display the Statement Type:

• Now, you have a lookup column in List 1 that references the statement type from List 2 based on the matching location and ID.
• Whenever you view List 1, you'll see the statement type corresponding to each location and ID.

By setting up the lookup column and calculated column in List 1, SharePoint will automatically retrieve the statement type from List 2 based on the matching location and ID whenever you add or edit items in List 1. This provides similar functionality to the Excel solution, allowing you to maintain a master list of statement types while matching data from another list. The text was created with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.