Returning a Value by Matching Two Columns of Data

[Christine Edwards]

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

 

[Biff]

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]

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,

 

[Biff]

Hi!

The Sumproduct formula does not need to be array entered but that won't
cause a problem.

The #N/A error means no matches were found and the #VALUE! error means ether
the ranges are of different sizes or the number values are really TEXT.

Some things to check:

The name columns may have unseen spaces or other unseen characters. You can
check this by testing some cells and making sure they actually do match. For
example:

Roster Database A2 = Jones
School 101 D2 = Jones

Test to see if they do in fact match:

='Roster Database'!A2='School 101'!D2

Should return TRUE

Test for unseen characters:

Roster Database A2 = Jones

=LEN('roster database'!A2)

Should return 5

Test that the numbers on Roster Database column C are really numbers:

=ISNUMBER('roster database'!c2)

Should return TRUE

If all else fails you can send me the file and I'll take a look. If you want
to do that just let me know how to contact you.

Biff

 

[Christine Edwards]

Hi Biff,

Thanks so much for your help. My problem was originated in not using the
CTRL,SHIFT,ENTER combination and once I did that and took the formula one
step at a time and instead of using an entire range for the items I wanted to
match to the formula worked. Thanks so much for your help and time

 

[Biff]

OK, glad you got it worked out!

Biff

Hi Biff,

Thanks so much for your help. My problem was originated in not using the
CTRL,SHIFT,ENTER combination and once I did that and took the formula one
step at a time and instead of using an entire range for the items I wanted
to
match to the formula worked. Thanks so much for your help and time