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:

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Jerry Sullivan

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

DemonCipher13

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

Jerry Sullivan

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

DemonCipher13

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

Jerry Sullivan

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

DemonCipher13

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

Jerry Sullivan

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

DemonCipher13

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

DemonCipher13

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

Watch MrExcel Video

Forum statistics

Threads
1,102,782
Messages
5,488,852
Members
407,658
Latest member
Arias610

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top