Page 1 of 3 123 LastLast
Results 1 to 10 of 26

Thread: Cross-Referencing Data in Different Worksheets
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    May 2014
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Cross-Referencing Data in Different Worksheets

    I have three worksheets I'm trying to work from. We will call them Worksheet 1, 2, and 3.

    I am trying to fill a column in worksheet 1, we will call it "Column B" for accuracy purposes.

    In order to fill this column, I would like to cross reference the data from Worksheets 2 and 3, and if a match is found, transpose it into "Column B" from Worksheet 1.

    For example. On Worksheet 2, "Column B," I have a string of data. Let's call this E, F, G, H, and I. And on the same worksheet, in "Column A," I have a particular reference number for each individual entry from "Column B." The same is the case with Worksheet 3, "Column A" and "Column B." What I want to do is this. If the data in Worksheet 2, "Column B" matches the data in Worksheet 1, "Column B," and/or if the data in Worksheet 3, "Column B" matches the data in Worksheet 1, "Column B," I want to be able to post a formula throughout Worksheet 1, "Column B," to cross reference both the other worksheets. This can also be done with just Worksheet 1 and Worksheet 2, or Worksheet 1 and Worksheet 3, I just don't want to have to enter 10,000 cells of individual data.

    I hope I made this clear.

    In other words, IF W2-CB matches W1-CC, then input equivalent row data from W2-CA into W1-CB.

    Maybe THAT will explain it better.
    Last edited by DemonCipher13; May 6th, 2014 at 03:53 PM. Reason: Clarity

  2. #2
    MrExcel MVP
    Moderator
    Jerry Sullivan's Avatar
    Join Date
    Mar 2010
    Location
    San Diego, California
    Posts
    8,787
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Cross-Referencing Data in Different Worksheets

    Quote Originally Posted by DemonCipher13 View Post
    In other words, IF W2-CB matches W1-CC, then input equivalent row data from W2-CA into W1-CB.
    Welcome to MrExcel,

    If I'm understanding your description, you could enter this formula in Sheet1 Cell B2 then copy it down as many rows as needed.

    =INDEX(Sheet2!$A:$A,MATCH($C2,Sheet2!$B:$B,0))
    Using Excel 2016

  3. #3
    New Member
    Join Date
    May 2014
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Cross-Referencing Data in Different Worksheets

    Quote Originally Posted by Jerry Sullivan View Post
    Welcome to MrExcel,

    If I'm understanding your description, you could enter this formula in Sheet1 Cell B2 then copy it down as many rows as needed.

    =INDEX(Sheet2!$A:$A,MATCH($C2,Sheet2!$B:$B,0))

    I have a feeling we are on the right track, but it kept returning N/A.

    I have tried something else to make it easier. All the sheets I need to cross-reference have now been copied into a single worksheet, however they are not titled Sheet 1, Sheet 2, etc. For sake of privacy - I work in the medical industry - we shall call them "Title 1" and "Title 2."

    I think part of the problem lies in my own understanding of it, so I'm gonna give the explanation another go. So please don't be offended by my redundancy.

    I have Columns A, B, and C, in "Title 1". Let's use row 5 as an example. Column A's data is irrelevant. Column B contains locations for samples listed in Column C. Each row in "Title 1" corresponds to the proper value, as it is the primary sheet.

    In "Title 2," I also have Columns A, B, and C. Again, Column A is irrelevant. Column B is, once again, locations for samples listed in Column C. In both of these worksheets, the individual rows are correctly paired with their corresponding locations, however, the rows are not the same in both worksheets. "Title 1" is significantly more populated than "Title 2." Now, in "Title B," the value I'm looking for to input into Row 5, Column B, "Title 1," will NOT be on Row 5, Column B, "Title 2." Most likely it will be on, let's call it, Row 10.

    In other words, if the number in "Title 2," Column C matches the number in "Title 1," Column C, then I want to put the corresponding row value - that's the value in "Title 2," Column B - into "Title 1," Column B.

    I think that explains it better.

  4. #4
    MrExcel MVP
    Moderator
    Jerry Sullivan's Avatar
    Join Date
    Mar 2010
    Location
    San Diego, California
    Posts
    8,787
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Cross-Referencing Data in Different Worksheets

    Those are different columns than in your Post #1 example, but the technique of finding the row number of the match, then using index to lookup a value on the same row as the match is the same.

    In Sheet Title 1, Cell B2 enter this formula then copy down...
    =INDEX('Title 2'!B:B,MATCH(C2,'Title 2'!C:C,0))

    To break that down into two parts, this should return "10" for the example you just described (for Row 10).
    =MATCH(C2,'Title 2'!C:C,0)

    This will return the 10th item in Sheet Title 2 Column B
    =INDEX('Title 2'!B:B, 10)
    Using Excel 2016

  5. #5
    New Member
    Join Date
    May 2014
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Cross-Referencing Data in Different Worksheets

    Quote Originally Posted by Jerry Sullivan View Post
    Those are different columns than in your Post #1 example, but the technique of finding the row number of the match, then using index to lookup a value on the same row as the match is the same.

    In Sheet Title 1, Cell B2 enter this formula then copy down...
    =INDEX('Title 2'!B:B,MATCH(C2,'Title 2'!C:C,0))

    To break that down into two parts, this should return "10" for the example you just described (for Row 10).
    =MATCH(C2,'Title 2'!C:C,0)

    This will return the 10th item in Sheet Title 2 Column B
    =INDEX('Title 2'!B:B, 10)

    Jerry, you have been a wonderful help so far!

    One final question, and I think we will have the answer I'm looking for.

    That function works perfectly for an individual cell, but if I paste down, the same value comes up. If I change that C value to my desired Cell in "Title 2," then the proper value I want comes up. What do I do to make that C value a range? In other words, every time I paste down the line, I want the function to search EVERY cell of "Title 2" for correspondence, so I don't have to look manually at every cell.

    The key is range for my C value. Once I have that, I have what I need.

    Thank you for all your help so far, again. I appreciate it!

  6. #6
    MrExcel MVP
    Moderator
    Jerry Sullivan's Avatar
    Join Date
    Mar 2010
    Location
    San Diego, California
    Posts
    8,787
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Cross-Referencing Data in Different Worksheets

    If you enter this formula in B2 then copy and paste it down, the C2 part will increment with each row C2, C3, C4....

    =INDEX('Title 2'!B:B,MATCH(C2,'Title 2'!C:C,0))

    If that didn't happen for you, they you may have added a "$" which will lock in the row 2 for each formula.

    This won't do what you want...
    =INDEX('Title 2'!B:B,MATCH(C$2,'Title 2'!C:C,0))
    Using Excel 2016

  7. #7
    New Member
    Join Date
    May 2014
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Cross-Referencing Data in Different Worksheets

    Quote Originally Posted by Jerry Sullivan View Post
    If you enter this formula in B2 then copy and paste it down, the C2 part will increment with each row C2, C3, C4....

    =INDEX('Title 2'!B:B,MATCH(C2,'Title 2'!C:C,0))

    If that didn't happen for you, they you may have added a "$" which will lock in the row 2 for each formula.

    This won't do what you want...
    =INDEX('Title 2'!B:B,MATCH(C$2,'Title 2'!C:C,0))

    What it's doing is pulling the equal row from each, rather than circumnavigating the rows for the corresponding value.

    It corresponds the value in "Title 2," Column B, Row 3500, to "Title 1," Column B, Row 3500.

    That's not exactly what I want.

    I want it to be able to search every row for the matches in "Title 1" and "Title 2" column C.

    In other words, how do I adjust the range of rows it searches for its match?

  8. #8
    MrExcel MVP
    Moderator
    Jerry Sullivan's Avatar
    Join Date
    Mar 2010
    Location
    San Diego, California
    Posts
    8,787
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Cross-Referencing Data in Different Worksheets

    Please post the formulas you have in Cells B2 and B3 of the Sheet we are calling Title 1, Copy and paste those directly with no edits since the problem you are having is may be a subtle difference in one character.

    If the sheet name that we are calling "Title 2" is something you don't want to post, then temporarily rename that sheet to Title 2 before copying the formulas instead of editing the formulas after copying.
    Using Excel 2016

  9. #9
    New Member
    Join Date
    May 2014
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Cross-Referencing Data in Different Worksheets

    Column B Column C
    H-5-1 05-14-A019c
    05-14-A020a

    This is how Title 1 is laid out.

    Column B Column C
    H-14-2 05-14-A020a
    H-6-7 05-14-A021d
    This is how Title 2 is laid out.

    Notice the missing value in Title 1, Column B. It is like that throughout the whole sheet. As I stated, this is the main sheet. I'm trying to group everything on this one sheet, move the data from Title 2 to Title 1, but the rows are dissimilar, i.e. it's row 1 in Title 2, but a completely different row in Title 1.

    I want to be able to find that hidden value in Title 1, Column B, using its match, 05-14-A020a, searching Title 2 for that particular number, and upon finding it, input the value from Title 2, Column B into Title 1 Column B. In other words, I want to scan the entirety of Title 2 for 05-14-A020a, and input it's location - H-14-2 - into Title 1.

  10. #10
    New Member
    Join Date
    May 2014
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Cross-Referencing Data in Different Worksheets

    And the formula I'm using is this: =INDEX('Title 2'!B:B,MATCH(C:C,'Title 1'!C:C,0))

    It will return the proper format value, as in H-#-#, but it won't return the proper corresponding value.

    And again, thank you for all your help so far.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •