Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Returning a Value by Matching Two Columns of Data
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Returning a Value by Matching Two Columns of Data
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Returning a Value by Matching Two Columns of Data
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Returning a Value by Matching Two Columns of Data
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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Returning a Value by Matching Two Columns of Data
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 -- Christine Edwards "Biff" wrote: 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" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Returning a Value by Matching Two Columns of Data
OK, glad you got it worked out!
Biff "Christine Edwards" wrote in message ... 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 -- Christine Edwards "Biff" wrote: 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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Matching two columns and their data | Excel Worksheet Functions | |||
ranking query | Excel Discussion (Misc queries) | |||
Data in narrow columns truncated when saving as DBF | Excel Discussion (Misc queries) | |||
How to take matching data from 2 columns and put in the same row? | New Users to Excel | |||
Pulling data from 1 sheet to another | Excel Worksheet Functions |