Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
match/index using multiple values
I found a few earlier posts, but I get error msgs when I try any of the
formulas suggested. I'm trying to use the values in 2 or 3 cells to match to the values in 2 or 3 columns to return a single value from another column. In my ex. below, I want to return count from the last column that matches the program value in A1 ("ProgramA", the date value in A2 and the value of "Denied"--so B2 would be 12. The number of rows in my lookup (D:G will vary). A B C D E F G ProgramA Total ProgramA Approved 4/1/04 25 6/1/04 ProgramB Approved 6/1/04 326 ProgramA Denied 6/1/04 12 ProgramB Denied 8/1/04 1 I have tried the following as array formulas and get either a #NUM! ("There is a problem with a number in your formula"): =index(G:G,match(A1&"Denied"&A2,D:D&E:E&F:F,0)) or #VALUE! error ("A value used in your formula is of the wrong data type"): =index(G:G,match(A1*"Denied"*A2,D:D*E:E*F:F,0)) Any suggestions? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
match/index using multiple values
When you are using array formulae, you cannot use complete columns, you must
use defined ranges, so try =index(G1:G1000,match(A1&"Denied"&A2,D1:D1000&E1:E 1000&F1:F1000,0)) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "perky2go" wrote in message ... I found a few earlier posts, but I get error msgs when I try any of the formulas suggested. I'm trying to use the values in 2 or 3 cells to match to the values in 2 or 3 columns to return a single value from another column. In my ex. below, I want to return count from the last column that matches the program value in A1 ("ProgramA", the date value in A2 and the value of "Denied"--so B2 would be 12. The number of rows in my lookup (D:G will vary). A B C D E F G ProgramA Total ProgramA Approved 4/1/04 25 6/1/04 ProgramB Approved 6/1/04 326 ProgramA Denied 6/1/04 12 ProgramB Denied 8/1/04 1 I have tried the following as array formulas and get either a #NUM! ("There is a problem with a number in your formula"): =index(G:G,match(A1&"Denied"&A2,D:D&E:E&F:F,0)) or #VALUE! error ("A value used in your formula is of the wrong data type"): =index(G:G,match(A1*"Denied"*A2,D:D*E:E*F:F,0)) Any suggestions? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
match/index using multiple values
Thank you so much! That did the trick! (And thanks for the earlier post on
the same subject you had in November that got me started. I haven't used arrays much but I can see where I'm going to have to change that tactic.) "Bob Phillips" wrote: When you are using array formulae, you cannot use complete columns, you must use defined ranges, so try =index(G1:G1000,match(A1&"Denied"&A2,D1:D1000&E1:E 1000&F1:F1000,0)) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "perky2go" wrote in message ... I found a few earlier posts, but I get error msgs when I try any of the formulas suggested. I'm trying to use the values in 2 or 3 cells to match to the values in 2 or 3 columns to return a single value from another column. In my ex. below, I want to return count from the last column that matches the program value in A1 ("ProgramA", the date value in A2 and the value of "Denied"--so B2 would be 12. The number of rows in my lookup (D:G will vary). A B C D E F G ProgramA Total ProgramA Approved 4/1/04 25 6/1/04 ProgramB Approved 6/1/04 326 ProgramA Denied 6/1/04 12 ProgramB Denied 8/1/04 1 I have tried the following as array formulas and get either a #NUM! ("There is a problem with a number in your formula"): =index(G:G,match(A1&"Denied"&A2,D:D&E:E&F:F,0)) or #VALUE! error ("A value used in your formula is of the wrong data type"): =index(G:G,match(A1*"Denied"*A2,D:D*E:E*F:F,0)) Any suggestions? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
match/index using multiple values
Hi,
You may also try the following array formula (Ctrl+Shift+Enter): =sum(if((D1:D4=A1)*(E1:E4="Denied")*(F1:F4=A2),G1: G4)) Regards, Ashish "perky2go" wrote: I found a few earlier posts, but I get error msgs when I try any of the formulas suggested. I'm trying to use the values in 2 or 3 cells to match to the values in 2 or 3 columns to return a single value from another column. In my ex. below, I want to return count from the last column that matches the program value in A1 ("ProgramA", the date value in A2 and the value of "Denied"--so B2 would be 12. The number of rows in my lookup (D:G will vary). A B C D E F G ProgramA Total ProgramA Approved 4/1/04 25 6/1/04 ProgramB Approved 6/1/04 326 ProgramA Denied 6/1/04 12 ProgramB Denied 8/1/04 1 I have tried the following as array formulas and get either a #NUM! ("There is a problem with a number in your formula"): =index(G:G,match(A1&"Denied"&A2,D:D&E:E&F:F,0)) or #VALUE! error ("A value used in your formula is of the wrong data type"): =index(G:G,match(A1*"Denied"*A2,D:D*E:E*F:F,0)) Any suggestions? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
match/index using multiple values
It's what we are here for <vbg
-- HTH Bob Phillips (remove nothere from email address if mailing direct) "perky2go" wrote in message ... Thank you so much! That did the trick! (And thanks for the earlier post on the same subject you had in November that got me started. I haven't used arrays much but I can see where I'm going to have to change that tactic.) "Bob Phillips" wrote: When you are using array formulae, you cannot use complete columns, you must use defined ranges, so try =index(G1:G1000,match(A1&"Denied"&A2,D1:D1000&E1:E 1000&F1:F1000,0)) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "perky2go" wrote in message ... I found a few earlier posts, but I get error msgs when I try any of the formulas suggested. I'm trying to use the values in 2 or 3 cells to match to the values in 2 or 3 columns to return a single value from another column. In my ex. below, I want to return count from the last column that matches the program value in A1 ("ProgramA", the date value in A2 and the value of "Denied"--so B2 would be 12. The number of rows in my lookup (D:G will vary). A B C D E F G ProgramA Total ProgramA Approved 4/1/04 25 6/1/04 ProgramB Approved 6/1/04 326 ProgramA Denied 6/1/04 12 ProgramB Denied 8/1/04 1 I have tried the following as array formulas and get either a #NUM! ("There is a problem with a number in your formula"): =index(G:G,match(A1&"Denied"&A2,D:D&E:E&F:F,0)) or #VALUE! error ("A value used in your formula is of the wrong data type"): =index(G:G,match(A1*"Denied"*A2,D:D*E:E*F:F,0)) Any suggestions? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
match/index using multiple values
Thanks--I haven't plugged this version in, but it looks like a good
alternative where I have a lot of "missing" month values from my data set that result in error messages that have to be cleaned up in the formula. The resulting cell formula will be a lot easier to follow for the next person looking at my template who is trying to figure out what I did! "Ashish Mathur" wrote: Hi, You may also try the following array formula (Ctrl+Shift+Enter): =sum(if((D1:D4=A1)*(E1:E4="Denied")*(F1:F4=A2),G1: G4)) Regards, Ashish "perky2go" wrote: I found a few earlier posts, but I get error msgs when I try any of the formulas suggested. I'm trying to use the values in 2 or 3 cells to match to the values in 2 or 3 columns to return a single value from another column. In my ex. below, I want to return count from the last column that matches the program value in A1 ("ProgramA", the date value in A2 and the value of "Denied"--so B2 would be 12. The number of rows in my lookup (D:G will vary). A B C D E F G ProgramA Total ProgramA Approved 4/1/04 25 6/1/04 ProgramB Approved 6/1/04 326 ProgramA Denied 6/1/04 12 ProgramB Denied 8/1/04 1 I have tried the following as array formulas and get either a #NUM! ("There is a problem with a number in your formula"): =index(G:G,match(A1&"Denied"&A2,D:D&E:E&F:F,0)) or #VALUE! error ("A value used in your formula is of the wrong data type"): =index(G:G,match(A1*"Denied"*A2,D:D*E:E*F:F,0)) Any suggestions? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count on multiple values with duplicate rows | Excel Worksheet Functions | |||
vlookup, multiple values, sum values into one cell?? | Excel Worksheet Functions | |||
how to lookup a value and return multiple corresponding values | Excel Worksheet Functions | |||
Using SumIF formulas with multiple lookup values | Excel Worksheet Functions | |||
Toggle multiple values in single cell | Excel Worksheet Functions |