Can this be done? Type of lookup

[Guest]

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.

 

[Bob Phillips]

Use a formula like

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

--

HTH

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

 

[Guest]

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]

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)

 

[Guest]

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.)