Cross-Referencing Data in Different Worksheets

DemonCipher13

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

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
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))
 
Upvote 0
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.
 
Upvote 0
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)
 
Upvote 0
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!
 
Upvote 0
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))
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
Column BColumn C
H-5-105-14-A019c
05-14-A020a

<tbody>
</tbody>

This is how Title 1 is laid out.

Column BColumn C
H-14-205-14-A020a
H-6-705-14-A021d

<tbody>
</tbody>
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,541
Latest member
iparraguirre89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top