Complicated vlookup example

liz123395 

it is possible to achieve this in Excel using a combination of functions such as INDEX, MATCH, and TEXTJOIN (if you are using Excel 2016 or later, or Excel for Microsoft 365). Here is how you can set it up:

Step-by-Step Solution

1. Prepare Your Data:
• Assume your names are in columns A, B, and C in Sheet1.
• Assume the list of all names and their corresponding groups are in columns A and B of Sheet2.
• We will retrieve the groups located 8 columns to the right of the names.
2. Formula to Retrieve Group Names:
• You need to create a formula that will find the group names corresponding to each of the three names and concatenate them.

Example

Assume the following structure:

Sheet1:

Sheet2:

Formula

In Sheet1, use the following formula to get the combined groups for the names in columns A, B, and C:

=TEXTJOIN(", ", TRUE, INDEX(Sheet2!I:I, MATCH(A1, Sheet2!A:A, 0)), INDEX(Sheet2!I:I, MATCH(B1, Sheet2!A:A, 0)), INDEX(Sheet2!I:I, MATCH(C1, Sheet2!A:A, 0)))

Explanation:

• MATCH(A1, Sheet2!A:A, 0): Finds the row number of the name in A1 within the range A:A in Sheet2.
• INDEX(Sheet2!I:I, MATCH(A1, Sheet2!A:A, 0)): Retrieves the value 8 columns to the right of the found row in Sheet2.
• TEXTJOIN(", ", TRUE, ...): Concatenates the results from the three INDEX functions, separated by ", ".

Steps to Implement:

1. Open Excel and navigate to Sheet1.
2. Select the cell where you want the combined group names to appear (e.g., D1).
3. Enter the formula above in the selected cell.
4. Press Enter to apply the formula. The cell will now display the concatenated group names for Sam, Sophia, and Liz.

Notes:

• Ensure that the columns in Sheet2 are appropriately aligned (i.e., the names are in column A and their groups are 8 columns to the right).
• The TEXTJOIN function is available in Excel 2016 and later versions. If you are using an earlier version, you will need to use an alternative method, such as concatenating the results manually or using helper columns.

This approach should help you dynamically search for names across three columns and retrieve and concatenate the corresponding group names from another sheet. The text and the steps are created with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and like it!

This will help all forum participants.