how to compare two columns on two sheets and copy associated data from one sheet to the other?

M

meghantrus

I have two sheets of data and Column A on both sheets has the same
type data (numbers) some numbers are the same on both sheets in Column
A, some are different, the numbers are not in the same order on both
sheets. Sheet1 also has a value in Columns Q & R in the same row that
is associated with Column A. I want to copy the value in Column Q&R
to Sheet 2 if the same number in column A exists on sheet 2. Can
anyone help me with this?
I have been trying to the use the VLOOKUP function , but am not having
any luck.

Sheet 1
Column A Column Q Column R
row1 5-123 test_01 details_and_code
row2 2-657 test_06 code
row3 5-1245 test_08 writing


Sheet 2
Column A Column Q Column R
row1 5-123
row2 4-4456
row3 5-1245
 
V

vezerid

=VLOOKUP(A2,'Sheet1'!A:Z,17,0) --- for column Q
=VLOOKUP(A2,'Sheet1'!A:Z,18,0) --- for column R

HTH
Kostis Vezerides
 
I

Ingolf

Hi,

=VLOOKUP($A1,Sheet1!$A$1:$R$1000,COLUMN(),0)

in cell Q1 should work. Align last row (above: 1000) with your data
range. Formula can be copied downwards and to the right with excel
adjusting the cell references itself.

=IF(ISERROR(VLOOKUP($A1,Sheet1!$A$1:$R$1000,COLUMN(),
0)),"",VLOOKUP($A1,Sheet1!$A$1:$R$1000,COLUMN(),0))

will return empty cells instead of error n/a for any number not to be
found on Sheet1.

Ingolf
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top