Thank you for the help but I am still having trouble. The formula came back
with a #N/A error using the index function and a #/Value using the sumproduct
function. I did use the CTRL,SHIFT,ENTER key combination when entering the
formulas. Any additional input that you might have would certainly be
appreciated.

Thank you,
--
Christine Edwards


"Biff" wrote:

Hi!

A couple of things:

The formula is an array formula an needs to be entered using the key
combination of CTRL,SHIFT,ENTER.

You left out the sheet references and the ranges don't correspond to your
explanation.

Try this (array entered):

=INDEX('roster database'!C$2:C$5,MATCH(D2&E2,'roster
database'!A$2:A$5&'roster database'!B$2:B$5),0)

However, if each name is only listed once this is better:

=SUMPRODUCT(--('roster database'!A$2:A$5=D2),--('roster
database'!B$2:B$5=E2),'roster database'!C$2:C$5)

Biff

"Christine Edwards" wrote in
message ...
In one sheet (School 101) I am trying to pull in the information in the
amount column using a formula that compares the information in both
columns
A&B from a sheet called Roster Database and returning the information
contained in column C when a match is found ie when a match is found for
DOE,
JAMES it returns $40,000. The information is set up as follows:

Roster Database
A B C
1 LAST FIRST AMOUNT
2 DOE JAMES 40,000
3 DOE JANE 20,000
4 DUNN BOB 35,500
5 GROW MIKE 10,000

School 101
D E F
1 LAST FIRST AMOUNT
2 DOE JANE
3 GROW MIKE

I have used the following function:
in Cell F2 =Index(C2:C5,Match(D1&E1,A2:A5&B2:B5),0))
Cell C2 should have $20,000 and C3 should have $10,000. When I use this
formula I am receiving the error #Value.

I hope that someone can help.

Thanks
--
Christine Edwards