Match and copy

  • Thread starter Thread starter Janette
  • Start date Start date
J

Janette

I'm looking for some help for the following.
I have two sheets in one workbook. One sheet contain the parcel number and
owner. The other sheet contains a parcel number (identical to the first
sheet) and an address. I need to lookup the parcel number and match it to
the other hence combinding the address and owner to the same parcel number.
Thanks in advance -and this is a great website containing loads of helps!
 
Hi,

Suppose the parcel numbers are in column A on both sheets with the address
in column B and in the other sheet the owner. Lets suppose there are title
on row 1 so the data starts on row 2.

In C2 on the sheet with the owner enter the following formula:

=VLOOKUP(A2,Sheet2!A$2:B$100,2,FALSE)

and copy it down
 
Another useful way to know is via index/match, which allows you to retrieve
it directly, regardless whether the matching col is to the right or left of
the col that you wish to return values from. Its more versatile than VLOOKUP
which requires the matching col to be always the leftmost col, with the
return-from col to the right.

Let's say you have in Sheet1
Col A = Parcel# (the match col)
Col B = Owner
Col C = Address (you want to populate this from Sheet2)

while in Sheet2, you have this:
Col B = Address (this is what you want to populate into Sheet1)
Col C = Parcel# (the match col)

In Sheet1,
Place this in C2:
=INDEX(Sheet2!B:B,MATCH(A2,Sheet2!C:C,0))
Copy down to return the required addresses

This: INDEX(Sheet2!B:B
is simply what you want to bring over

MATCH(A2,Sheet2!C:C,0)
matches the common: parcel#
It returns the relative row position which is passed to INDEX to extract
accordingly

And if you need an error trap so that non-matching cases will be returned
neatly as blanks:"", you could use this in C2:
=IF(ISNA(MATCH(A2,Sheet2!C:C,0)),"",INDEX(Sheet2!B:B,MATCH(A2,Sheet2!C:C,0)))

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
 
Back
Top