Cross-Referencing Data in Different Worksheets

[DemonCipher13]

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.

 

[Jerry Sullivan]

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))

 

[DemonCipher13]

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]

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

It will provide the result you want.

 

[DemonCipher13]

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]

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?

 

[Jerry Sullivan]

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.

 

[DemonCipher13]

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

<tbody>
</tbody>

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

<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]

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?

 

[DemonCipher13]

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]

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"?