In Excel How do I cross reference 2 columns and some?

Question

I have two Excel documents both with multiple rows and columns.One of the columns in both the documents contains the same type of information. I would like excel to cross reference these columns. If it the finds any exact matches I would like it to the place the data from two other columns into the destination sheet, but this information would have to correspond to the correct row where it found the match.

If anyone can help me with this it would be greatly appreciated even if its just a pointer in the right direction.

Thanks.

Answer 1

I agree, the vba answer is way too complex... vlookup is the way to go. Here's how:



EDIT: Sorry about the spacing, I can't get yahoo to format this correctly, but each letter should be a seperate column.



First (target) sheet

Column A Column B Column C

1 One w p

2 Two x q

3 Three y r

4 Four z s



Second (destination) sheet (after entering Vlookup formula)

Column A Column B Column C

1 One w p

2 Three x q

3 Five #N/A #N/A

4 Seven #N/A #N/A



Your formula in column B of your target sheet is =VLOOKUP(A1,target!A:B,2,FALSE)

Your formula in column C is =VLOOKUP(A1,target!A:C,3,FALSE)



Now just replace the #N/A's or delete them.



Those samples and the help for VLOOKUP should be able to get you through this one so you can make the appropriate substitutions on your sheet.



Good luck

Answer 2

If this column has a unique value, then you can do this:

Do a third table in a third sheet with that column as first column, then use INDEX + MATCH + other functions to bring the values from both sheets

The functions you will need are:

INDEX, MATCH to bring values

ADDRESS, INDIRECT to read from different sheets at the same time

OFFSET, COUNTA to allow the changing



I would do it for you here, but you didn't provide any further info for me to help



It will looks like almost creating a third table from two tables in the sequence that you want



after doing all this, do copy > Paste Values and get rid of the source two tables



If this columns has not a unique value, or you want it in a different way, we can do the macro way



If you need help doing all this (in functions or macros), I am here to help you for free, mail me here or

[e-mail]



VBAXLMan is back to feed your Excel needs

Answer 3

lol the guy above me made this hella hard. Try a vlookup formula. Vlookups let you reference between 2 sheets and return whichever data u want. if you need further help, email me at [e-mail].

Answer 4

Paste the formula below in column C. After pasting, press CTRL+SHIFT+ENTER, not just ENTER. Drag the formula down until you start getting blanks. =IF(ROW(Z1)>SUM(COUNTIF(B$1:B$1500, A$1:A$1500)),"",INDEX(A:A, SMALL(IF(COUNTIF(B$1:B$1500, A$1:A$1500)>0,ROW($1:$1500)),ROW(Z1))))