An easier way ??

Hi, I'm wondering if there is an easy way of filling in cells that ccorrespond with another .xls I have.

Here is the scenario.....

Book#1 has column A (Last name) and column B (first initial)

Column C & D are blank but I want to enter employee #'s/card#'s in those columns.

Now I have Book#2 with column A (Last name, first name) in the same cell

Columns B & C from Book #2 need to go into Book # 1 in columns C & D in Book#1.

My question is...is there a way for excel to look for the exact last name and enter these 2 columns of #'s automatically?
Or do I have to do this manually.

FYI.......there are more names in Book # 1 than Book #2 so it's not a matter of copying/pasting.

Any help will be greatly appreciated

Assuming the lastname first initial is unique this should work

=INDEX([Book2]Sheet1!B:B,SUMPRODUCT(--([Book2]Sheet1!A1:A10<>""),--(LEFT([Bo
ok2]Sheet1!A1:A10,LEN(A1)+3)=A1&", "&B1),--ROW([Book2]Sheet1!A1:A10)))

and

=INDEX([Book2]Sheet1!C:C,SUMPRODUCT(--([Book2]Sheet1!A1:A10<>""),--(LEFT([Bo
ok2]Sheet1!A1:A10,LEN(A1)+3)=A1&", "&B1),--ROW([Book2]Sheet1!A1:A10)))

--
HTH

Bob Phillips

"Fitty" <[email protected]> wrote in
message news:[email protected]...
>
> Hi, I'm wondering if there is an easy way of filling in cells that
> ccorrespond with another .xls I have.
>
> Here is the scenario.....
>
> Book#1 has column A (Last name) and column B (first initial)
>
> Column C & D are blank but I want to enter employee #'s/card#'s in
> those columns.
>
> Now I have Book#2 with column A (Last name, first name) in the same
> cell
>
> Columns B & C from Book #2 need to go into Book # 1 in columns C & D
> in Book#1.
>
> My question is...is there a way for excel to look for the exact last
> name and enter these 2 columns of #'s automatically?
> Or do I have to do this manually.
>
> FYI.......there are more names in Book # 1 than Book #2 so it's not a
> matter of copying/pasting.
>
> Any help will be greatly appreciated
>
>
> --
> Fitty
> ------------------------------------------------------------------------
> Fitty's Profile:
http://www.excelforum.com/member.php...fo&userid=8029
> View this thread: http://www.excelforum.com/showthread...hreadid=474510
>