An easier way ??

  • Thread starter Thread starter Fitty
  • Start date Start date
F

Fitty

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)))
 
Back
Top