Forum Discussion

liz123395's avatar
liz123395
Copper Contributor
May 17, 2024

Complicated vlookup example

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. 

  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor

    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.

Resources