excelexcel-formula

What Excel formula could I use to find the reference ID for a specific cell, given part of the text contained in that cell? like CTRL+F

I am trying to correlate the ID's between two sheets in excel. Here are screenshots of a simplified version of my excel file.

Sheet1: Column A is ID numbers 1 through 14. Column B lists names unique to each ID number. Column C is where I'm trying to put the Index Number from Sheet2.

Sheet1

Sheet2: Columns A through F are an Excel version of a family tree with common ancestors and their decendants in the column to their right and down one. Each name in the tree is unique and contains more characters than the names from Sheet1, but most of them are the same names from Sheet 1. Most of the cells are blank. Column G is the Row Index for each layer of the family tree. Column H is where I want to put the ID number from Sheet1.

Sheet2

My end goal is to have the Sheet1 ID's show up on each relevant row in Sheet2 column H.

I've tried to make a formula in Sheet2 H2 look for a cell between A2 and F2 with text in it, then try to find that text in column B of Sheet1, and returns the corresponding index number from Sheet1 A. Unfortuantly, I realized that most of the text strings on Sheet2 have more information in them than just the name from Sheet1, so looking for a match doesn't work. Now I'm trying a work around where Sheet1 cell C2 uses the name found in B2 to look in Sheet2 columns A-F for a match. Once its found a match, it looks at Sheet2's index number in column G and returns that.

I'm thinking that once I have a way to correlate the Sheet2 Index Number with the Sheet1 Name/ID, I can more easily go the other direction and correlate the Sheet1 ID with the name listed on Sheet2.

I've tried VLOOKUP, INDEX and MATCH, and swapping which sheet searches the other but haven't been able to code the right formula to do what I'm trying to do.

I expected that VLOOKUP would work, until I found out it doesn't work between sheets.

I tried INDEX and MATCH, but it seems like that only works if you have something that can work as an x and y axis for the table.

CTRL+F is all manual labor and I'm working with a big enough dataset that it would be days worth of work.

SEARCH can only look at specific cells, not an entire array.

I made some headway swapping which sheet searches the other, but even though it simplified the varaibles, the formula itself still made a mess.

Any ideas for how I can get this to work?

Solution

  • You could try XLOOKUP with wildcard:

    Enter the following in H1:

    Short version:

    =XLOOKUP(
    G2:G22,
    XLOOKUP("*" & Sheet1!B2:B15 & "*", TOCOL(A2:D22, 1), G2:G22, , 2),
    Sheet1!A2:A15,
    ""
)

    Preferred:

    =LET(
    sheet1, Sheet1!A2:B15,
    sh1_id, INDEX(sheet1, , 1),
    sh1_name, INDEX(sheet1, , 2),
    sh2_name, TOCOL(A2:D22, 1),
    sh2_index, G2:G22,
    sh2_idx_for_sh1_name, XLOOKUP(
        "*" & sh1_name & "*",
        sh2_name,
        sh2_index,
        ,
        2
    ),
    XLOOKUP(sh2_index, sh2_idx_for_sh1_name, sh1_id, "")
)

    Result