Complicated vlookup example

[liz123395]

Hi there, 

I have a dataset where there are three different columns with names. I would like VLOOKUP or INDEX, etc to use those names (in 3 different columns) and search for all 3 against one column in another sheet. Once the matches are found, I'd like to get the contents in the column 8 rows to the right for all 3 names in one cell (so merged). Example, if I have names Sam, Sophia and Liz and the columns 8 rows to the right had the following 

Sam - Jungle Group

Sophia - Safari Group

Liz - Forest Group

I'd like the input to return to be Jungle Group, Safari Group, Forest Group

Let me know if this is possible. 

[#2]

@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:

A	B	C
Sam	Sophia	Liz
...	...	...

Sheet2:

A	...	I
Sam	...	Jungle Group
Sophia	...	Safari Group
Liz	...	Forest Group
...	...	...

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.