Can this be done? Type of lookup
 

I have two worksheets:
Name ID ABC DEF GHI
Abe Lincoln AL
John Hancock JK
Mark Twain MT

ID DED AMT
AL ABC 4.5
AL GHI 6.7
JK DEF 4
JK GHI 7
MT ABC 4.5

I would like to have Excel match the ID from the second spreadsheet and
place the AMT in the correct column in the first spreadsheet. In other words,
4.50 would appear in the ABC column on Abe Lincoln's line and 6.70 in the GHI
column.

Not all people have the same deductions, nor are they of the same amount. I
do appreciate any advice anyone has.

Can this be done? Type of lookup
 

Use a formula like

=VLOOKUP(B2,Sheet!$A$1:$CO$20,3,True)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Kmagg" wrote in message
...
I have two worksheets:
Name ID ABC DEF GHI
Abe Lincoln AL
John Hancock JK
Mark Twain MT

ID DED AMT
AL ABC 4.5
AL GHI 6.7
JK DEF 4
JK GHI 7
MT ABC 4.5

I would like to have Excel match the ID from the second spreadsheet and
place the AMT in the correct column in the first spreadsheet. In other
words,
4.50 would appear in the ABC column on Abe Lincoln's line and 6.70 in the
GHI
column.

Not all people have the same deductions, nor are they of the same amount.
I
do appreciate any advice anyone has.

Can this be done? Type of lookup
 

Thanks for the input, but I pasted that formula and received a #NA message.
Where would I put the worksheet names and would I copy this formula to all of
the columns (ABC, DEF, GHI, etc.)?

"Bob Phillips" wrote:

Use a formula like

=VLOOKUP(B2,Sheet!$A$1:$CO$20,3,True)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Kmagg" wrote in message
...
I have two worksheets:
Name ID ABC DEF GHI
Abe Lincoln AL
John Hancock JK
Mark Twain MT

ID DED AMT
AL ABC 4.5
AL GHI 6.7
JK DEF 4
JK GHI 7
MT ABC 4.5

I would like to have Excel match the ID from the second spreadsheet and
place the AMT in the correct column in the first spreadsheet. In other
words,
4.50 would appear in the ABC column on Abe Lincoln's line and 6.70 in the
GHI
column.

Not all people have the same deductions, nor are they of the same amount.
I
do appreciate any advice anyone has.

Can this be done? Type of lookup
 

That means that the value being looked up wasn't found. Is this expected, if
so use

=IF(ISNA(formula),"",formula)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Kmagg" wrote in message
...
Thanks for the input, but I pasted that formula and received a #NA
message.
Where would I put the worksheet names and would I copy this formula to all
of
the columns (ABC, DEF, GHI, etc.)?

"Bob Phillips" wrote:

Use a formula like

=VLOOKUP(B2,Sheet!$A$1:$CO$20,3,True)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Kmagg" wrote in message
...
I have two worksheets:
Name ID ABC DEF GHI
Abe Lincoln AL
John Hancock JK
Mark Twain MT

ID DED AMT
AL ABC 4.5
AL GHI 6.7
JK DEF 4
JK GHI 7
MT ABC 4.5

I would like to have Excel match the ID from the second spreadsheet
and
place the AMT in the correct column in the first spreadsheet. In other
words,
4.50 would appear in the ABC column on Abe Lincoln's line and 6.70 in
the
GHI
column.

Not all people have the same deductions, nor are they of the same
amount.
I
do appreciate any advice anyone has.

Can this be done? Type of lookup
 

It took me a while, but I finally got this to work. I admit I did not
understand the use of the sumproduct function, but after looking at Bob
Philip's site, I got this to work. The following formula is in sheet 1 with
the values to lookup in sheet2.

=SUMPRODUCT(--(Sheet2!$A$2:$A$6=Sheet1!$B2),--(Sheet2!$B$2:$B$6=Sheet1!C$1),(Sheet2!$C$2:$C$6))

thus in worksheet 1 is:
Name ID ABC DEF GHI
Abe Lincoln AL
John Hancock JK
Mark Twain MT
and in worksheet 2 is :
ID DED AMT
AL ABC 4.5
AL GHI 6.7
JK DEF 4
JK GHI 7
MT ABC 4.5

BTW, not sure if I need to refer back to sheet1, but since I entered the
formula by pointing to the areas that I wanted to use, that is what was
entered (and then I went back in and changed relative to absolute where
necessary with the F4 key.)

"Kmagg" wrote:

I have two worksheets:
Name ID ABC DEF GHI
Abe Lincoln AL
John Hancock JK
Mark Twain MT

ID DED AMT
AL ABC 4.5
AL GHI 6.7
JK DEF 4
JK GHI 7
MT ABC 4.5

I would like to have Excel match the ID from the second spreadsheet and
place the AMT in the correct column in the first spreadsheet. In other words,
4.50 would appear in the ABC column on Abe Lincoln's line and 6.70 in the GHI
column.

Not all people have the same deductions, nor are they of the same amount. I
do appreciate any advice anyone has.