Cross-Referencing Data in Different Worksheets

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
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))
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

DemonCipher13

New Member
Joined
May 6, 2014
Messages
14
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.
 

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
Have you tried copying down the formula exactly as I suggested?

It will provide the result you want.
 

DemonCipher13

New Member
Joined
May 6, 2014
Messages
14
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.
 

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
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:

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
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:

DemonCipher13

New Member
Joined
May 6, 2014
Messages
14
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 BColumn C
H-1-301-01-A001a
H-3-901-02-A002a
H-5-601-03-A003a
01-04-A004a

<tbody>
</tbody>

Column BColumn C
H-10-401-04-A004a
H-14-201-05-A005a
H-5-501-06-A006a
H-5-101-07-A007a

<tbody>
</tbody>

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.
 

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
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:

DemonCipher13

New Member
Joined
May 6, 2014
Messages
14
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.
 

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
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"?
 

Watch MrExcel Video

Forum statistics

Threads
1,102,134
Messages
5,484,932
Members
407,474
Latest member
Pam Sander

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top