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.