Cross-Referencing Data in Different Worksheets
Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 26

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

  1. #11
    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
    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.
    You modified the formula I suggested. You're using
    =INDEX('Title 2'!B:B,MATCH(C:C,'Title 1'!C:C,0))

    In Title 1, Cell B2 paste this...

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

    When you copy that down to Title 1, Cell B3 it will increment to this....
    =INDEX('Title 2'!B:B,MATCH(C3,'Title 1'!C:C,0))
    Using Excel 2016

  2. #12
    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

    Yes I did. Because using individual cells like that would be useless. It would yield the same results as just manually looking for every value.

    That's the part I want to avoid. I want to search the entire C range of cells in Title 2 for matching value in Title 1, and plug in the location - column B from Title 2 - to Title 1.

    I want it to scan the entire document for the matching row. Not just the cell I designate. Inputting C1 or C2 will only input the value for C1 or C2, respectively. If, however, I input the entire C range, C:C, then theoretically, it should search the entire range.

    However, I am still getting the same result, as in, it is pulling the value from the current row I'm working on from Title 2 and placing it into Title 1, i.e. the data from Row 35, Title 2, is being input into Row 35, Title 1, even though the correct number that goes into Row 35, Title 1, is actually located on Row 301, Title 2.

  3. #13
    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

    Have you tried copying down the formula exactly as I suggested?

    It will provide the result you want.
    Using Excel 2016

  4. #14
    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

    Yes, I did. Exactly as written.

    But with every single entry, I'd have to change the C value to the corresponding value in the other worksheet. That makes no sense when I could just manually look for the number and input it by hand.

    I want to consolidate. I want to skip the part of re-entering the C value. I'm working with well over 10000 rows of data here.

    I want that C value to be all-encompassing, so that I'll have to paste a single function, no edits, and get the value that I want.

    If the value for Title 1, column B, row 1700, just so happens to be in Title 2, column B, row 5423, I want it to find that particular value in that document, and input it where I've inserted my formula.

  5. #15
    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

    So when you used the formula exactly as suggested, what was the formula in B2 and what was the resulting formula in B3 after you copied it from B2?
    Last edited by Jerry Sullivan; May 8th, 2014 at 12:12 PM.
    Using Excel 2016

  6. #16
    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

    But with every single entry, I'd have to change the C value to the corresponding value in the other worksheet. That makes no sense when I could just manually look for the number and input it by hand.

    I want to consolidate. I want to skip the part of re-entering the C value. I'm working with well over 10000 rows of data here.

    I want that C value to be all-encompassing, so that I'll have to paste a single function, no edits, and get the value that I want.

    If the value for Title 1, column B, row 1700, just so happens to be in Title 2, column B, row 5423, I want it to find that particular value in that document, and input it where I've inserted my formula.
    Just in case it might feel like I'm not listening to you- I understand what you are describing above. I wouldn't suggest something that involved you manually editing the formula on each row. I also understand that the entire Title 2 Column C needs to be searched. That's what this part of the formula does.
    =INDEX('Title 2'!B:B,MATCH(C2,'Title 1'!C:C,0))

    Each one formula in Column B is only needing to search all of Title 2 Column C to find one value. The value on the same row as the formula.

    If you copy that formula down, each of the 10,000 formulas that result will search All of Title 2 Column C, find the first matching value (regardless of what row), then return the value in Title 2 Column B of the same row that the match was found.
    Last edited by Jerry Sullivan; May 8th, 2014 at 12:28 PM.
    Using Excel 2016

  7. #17
    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
    So when you used the formula exactly as suggested, what was the formula in B2 and what was the resulting formula in B3 after you copied it from B2?
    Column B Column C
    H-1-3 01-01-A001a
    H-3-9 01-02-A002a
    H-5-6 01-03-A003a
    01-04-A004a

    Column B Column C
    H-10-4 01-04-A004a
    H-14-2 01-05-A005a
    H-5-5 01-06-A006a
    H-5-1 01-07-A007a

    Top table is Title 1, Bottom Table is Title 2.

    I am trying to fill in Title 1, row 4, Column B.
    The corresponding value is in Title 2, row 1, Column B.

    Note the row difference.

    When I input this formula:

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

    The value that will be put in will be H-14-2, the corresponding B value to cell C2 in Title 2. However, that is NOT the value that I want. I want the value H-10-4, in Title 2, Row 1, Column B, to go into the blank.

  8. #18
    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

    I think I see our miscommunication. It sounds like you already have some constant values in Title 1 Column B (like B2,B3 in your last example), and you are trying to put formulas just in the blanks (like B4).

    Is that correct?

    My suggestion was based on entering the formula in B2 and copying it down to B3:B1000. This will cause the formula in Title 1 B4 to be...

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

    and the formula in B10000 to be
    =INDEX('Title 2'!B:B,MATCH(C10000,'Title 1'!C:C,0))

    If your constant values currently entered in B2 and B3 can be looked up in Title 2 doing the process I describe it will overwrite the constant with a formula that returns the same value (provided that you had consistent data). If the lookup finds a different value- you might be correcting an incorrect value that was manually entered. For most applications, it is better to have one consistent formula copied down a range of like items than to mix formulas and constants.

    If there's some reason you can't lookup the values that have already been entered in Col B there's alternatives - but before I explain that, will overwriting the existing constants with lookup formulas the should return the same value work for you?
    Last edited by Jerry Sullivan; May 8th, 2014 at 01:10 PM.
    Using Excel 2016

  9. #19
    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
    I think I see our miscommunication. It sounds like you already have some constant values in Title 1 Column B (like B2,B3 in your last example), and you are trying to put formulas just in the blanks (like B4).

    Is that correct?

    My suggestion was based on entering the formula in B2 and copying it down to B3:B1000. This will cause the formula in Title 1 B4 to be...

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

    and the formula in B10000 to be
    =INDEX('Title 2'!B:B,MATCH(C10000,'Title 1'!C:C,0))

    If your constant values currently entered in B2 and B3 can be looked up in Title 2 doing the process I describe it will overwrite the constant with a formula that returns the same value (provided that you had consistent data). If the lookup finds a different value- you might be correcting an incorrect value that was manually entered. For most applications, it is better to have one consistent formula copied down a range of like items than to mix formulas and constants.

    If there's some reason you can't lookup the values that have already been entered in Col B there's alternatives - but before I explain that, will overwriting the existing constants with lookup formulas the should return the same value work for you?

    I think you've hit the nail on the head.

    Okay. My Title 1 worksheet is a list of tissues. I work at a banking facility, and the objective is to collect various tissues from various lists of storage - freezer storage - and compile it into this list. Column B in Title 1 is actually the location of these samples, the samples, themselves, represented by the "01-01-A001a" syntax in column C. These are unchangeable. I am pulling from the second spreadsheet, Title 2. Column B in Title 2 is the location of the samples, or Column C of title 2. These are vastly different lists, with only some of the numbers being the same. In other words, EVERY sample is found in Title 1. But only a fraction of those are found in Title 2. I'm trying to compile Title 2's list into Title 1. Each row is it's proper value, and Title 2 is completely populated. Title 1, however, is only sparsely populated. The eventual goal is to have every location from Title 2 input into Title 1. So, as you can see, uneditable.

    Does that explain the predicament a little better? It's just a matter of association.

  10. #20
    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

    Okay, now it's clearer.

    I'll suggest some steps to look up just the matches into just the blank cells.

    Do you know how to use Autofilters to show just the rows with blanks in Title1 Column B?

    For blank cells in col B that don't have matches, do you want those to remain blank or read "no match"?
    Using Excel 2016

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
  •