Search and add corresponding data

  • Thread starter Thread starter Joe
  • Start date Start date
J

Joe

I have a worksheet with a bunch of data in it. The first
row consists of student ID numbers. The rest of it is
just information about the students. In another worksheet
I have another column of data which I would like to insert
into the first worksheet. Unfortunetly, both data sets
are not sorted, nor can they be sorted because it would
screw up the rest of the data. Basically, I need a way to
search 2 columns for data, and when it finds a match, take
the data from the column on the right side of the second
sheet, and place it with the corresponding data on the
first sheet. For example:

Sheet 1 Sheet 2

A B A B
1 356 1 569 583824
2 482 2 356 4588618
3 569 3 1798 638841
4 1798 4 482 355471

So I would need to search column A of both worksheets and
when I find a match, take the data from the corresponding
cell in column B of worksheet 2 and put it into column B
of worksheet 1. Any suggestions?
 
Hi Joe

use VLOOKUP
in column B on sheet A enter the following
=VLOOKUP(A1,'Sheet 2'!$A$1:$B:9999,2,FALSE)

HTH
Frank
 
Hi Joe

Mark the whole range of information on sheet 2 and Name it as Data (just
click in the white pane above row 1 and to the left of column A and type
Data)

On sheet 1 in cell B1 enter
=VLOOKUP(A1,data,2,0)

Copy down column B as far as required
 
That worked great! Thanks. But now when a value doesn't
exist in the A column of sheet 2, but does in sheet 1, it
gives me a #N/A. Is there anyway that I could just skip
over that value and leave the cell blank?
 
That almost worked. After looking at the formula, it
seems like the array being searched in the Match function
is changing when I copy\paste it into other cells. For
instance if I pasted the formula from A1 to A2, the
formula changes to :
=IF(ISNA(MATCH(A2,'Sheet2'!A2:A201,0)"",VLOOKUP
(A2,'Sheet2'!A2:B201,2,0)

I tried changing the Match part of the formula to:
MATCH(A2,'Sheet2'!A:B,0)
but that would make the ISNA return True, and would make
the if statement true, when it would need to be false.
Any suggestions?
 
Nevermind. I figured it out. I made the range Sheet2!
A:A, and that worked. Thanks for all your help guys!!
 
Back
Top