Text and Numeric lookup

[Cynthia]

I'm using MS Office Professional 2003.
I am frequently required to compile mailing lists from Excel spreadsheets
containing extensive employee data.

Situation:
I have 2 worksheets in 1 workbook

Sheet 1 has ID#, last name, first name, address1, address2, city, state, zip
for all employees (~1000)

Sheet 2 has ID#, last name, first name of employees receiving award letter
(~200)

How do I lookup and retrieve address information for award letter recipients
from Sheet 1 and have that information correctly match and populate
horizontally across cells in Sheet 2?

EX:
Sheet 1
Col A Col B Col C Col D Col E Col F
Col G Col H
12345 Jones David 465 Main St. #1 Toledo OH
55555
25283 Baker Linda 7324 Elm St. Stamford CT
55555


Sheet 2
Col A Col B Col C Col D Col E Col F
Col G Col H
12345 Jones David
25283 Baker Linda

Thank you in advance!

 

[Phil]

Hi Cynthia,

In sheet 2, col D, insert a lookup formula... =VLOOKUP(A2,[the range
of data in Sheet1),4,FALSE

So, for the house number, you would have =VLOOKUP(A2,Sheet1!
A11000,4,FALSE)

This picks up the ID# in your awards sheet, compares it to the range
you've specified, to find the ID number in the first column of the
adresses sheet, then finds the entry in column 4 of that range. The
"FALSE" at the end of the formula ensures that if no exact match is
found, an #N/A error results.

Hope that helps

Phil

 

[Cynthia]

Phil,
Thank you - this works great! One question - is there a way to amend the
command so that I can copy it across columns and have the Sheet 1 secondary
column reference and location number change automatically (i.e.: from
H39:L1000, 5, false to H39:L1000, X, false)? As it is, I have to do that
manually each time I copy over to get address2, city, state, zip.

Thank you,
Cynthia

 

[Phil]

Hi Cynthtia,

I don't think there's a way to automatically up date the column number
as you copy across the sheet - it's just a number as far as Excel is
concerned and thus doesn't increment.

However you need to make the range refrences absolute befor you copy,
otherwise they will increment. Change your formula so they read $H
$39:$L$1000, and also for the first cell reference in the formula.

Actually, there are a couple of tricks that will at least reduce your
editing....

1 - In the first formula cell, highlight the range reference cells in
the formula bar, then press F4 (This will add the dollar signs). The
formula should look something like
=VLOOKUP($A$1,$H$1:$L$1000,5,FALSE), with your own cell references in,
of course!

Press Enter.

2 - Use the Autofill handle to copy the formula to the rest of the
row, then edit the colum numbers manually (a pain I know, but it's the
only manual editing you'll need to do!)

3 - When finished, highlight the entire row containing the formulas
and use the Autofill handle on the last highlighted cell and copy down
as far as you need. This will copy the lot.

4. Use Search/replace to edit the $ signs out of the first cell
reference in the formulae (the $A$1) to make it $A1.

And that should do it all a bit quicker!

Hope that helps,

Phil

 

[Phil]

Woops - DON'T do the search/replace thing - Just put a $ in front of
the A in the first formula before you copy horizontally. The column
letter won't change because of the $, and neither wil the row no's
'cos your filling horizontally. However you do need them to change
when you copy the lot vertically.

Hope that makes some sense!

Phil

 

[Cynthia]

Phil,
Thank you - all good info, especially the last post. This will save me
hours of time and cross-checking. I appreciate your help!!

Cynthia