Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
MATCH and INDEX combination using multiple criteria?
I'm combining the INDEX function with the MATCH function to do a lookup, but
I have multiple criteria. (I can't use the DGET function because it requires a two row criteria range, which won't work for my situation). Can multiple criteria work with the MATCH and INDEX combo? Or is there a different way to do what I want to do?: A B C 1 Name Date Amount 2 Joe 1 20.00 3 Joe 3 30.00 4 Joe 7 15.00 5 Ian 1 12.00 6 Ian 3 5.00 If my criteria is Joe, 7, I'd like Excel (2007) to return 15.00. My one criteria INDEX and MATCH formula looks like this: =INDEX(A1:C6,MATCH("Joe",A:A,0),3) But it returns 20.00, the first instance of Joe. Thanks. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
MATCH and INDEX combination using multiple criteria?
You can do it easily with SUMPRODUCT or with a SUM(IF( array formula. An
array formula must be entered by pressing CTRL+Shift+Enter and not just Enter. If you do it correctly then Excel will put curly brackets around the formula {}. You can't type these yourself. If you edit the formula you must enter it again with CTRL+Shift+Enter. The SUMPRODUCT formula: =SUMPRODUCT(--(A1:A6="Joe"),--(B1:B6=7),C1:C6) The SUM(IF( array formula: {=SUM(IF((A1:A6="Joe")*(B1:B6=7),C1:C6,0))} Bob Phillips explains =sumproduct() in more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html Hope this helps, Hutch "John" wrote: I'm combining the INDEX function with the MATCH function to do a lookup, but I have multiple criteria. (I can't use the DGET function because it requires a two row criteria range, which won't work for my situation). Can multiple criteria work with the MATCH and INDEX combo? Or is there a different way to do what I want to do?: A B C 1 Name Date Amount 2 Joe 1 20.00 3 Joe 3 30.00 4 Joe 7 15.00 5 Ian 1 12.00 6 Ian 3 5.00 If my criteria is Joe, 7, I'd like Excel (2007) to return 15.00. My one criteria INDEX and MATCH formula looks like this: =INDEX(A1:C6,MATCH("Joe",A:A,0),3) But it returns 20.00, the first instance of Joe. Thanks. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
MATCH and INDEX combination using multiple criteria?
I can think of a couple things:
With 'Joe' in F1 and 'Amount' in F2 F3 =INDEX(A1:C7,MATCH(F1,A1:A7,1),MATCH(F2,A1:C1,0)) Or... =SUMPRODUCT(--(A1:A7="Joe"),--(B1:B7=7),C1:C7) HTH, Ryan-- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Tom Hutchins" wrote: You can do it easily with SUMPRODUCT or with a SUM(IF( array formula. An array formula must be entered by pressing CTRL+Shift+Enter and not just Enter. If you do it correctly then Excel will put curly brackets around the formula {}. You can't type these yourself. If you edit the formula you must enter it again with CTRL+Shift+Enter. The SUMPRODUCT formula: =SUMPRODUCT(--(A1:A6="Joe"),--(B1:B6=7),C1:C6) The SUM(IF( array formula: {=SUM(IF((A1:A6="Joe")*(B1:B6=7),C1:C6,0))} Bob Phillips explains =sumproduct() in more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html Hope this helps, Hutch "John" wrote: I'm combining the INDEX function with the MATCH function to do a lookup, but I have multiple criteria. (I can't use the DGET function because it requires a two row criteria range, which won't work for my situation). Can multiple criteria work with the MATCH and INDEX combo? Or is there a different way to do what I want to do?: A B C 1 Name Date Amount 2 Joe 1 20.00 3 Joe 3 30.00 4 Joe 7 15.00 5 Ian 1 12.00 6 Ian 3 5.00 If my criteria is Joe, 7, I'd like Excel (2007) to return 15.00. My one criteria INDEX and MATCH formula looks like this: =INDEX(A1:C6,MATCH("Joe",A:A,0),3) But it returns 20.00, the first instance of Joe. Thanks. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
MATCH and INDEX combination using multiple criteria?
Hello,
For one special lookup you got the SUMPRODUCT hint already. If you want it auto-updating for all your combinations have a look at my UDF Sfreq, please: http://www.sulprobil.com/html/sfreq.html Regards, Bernd |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match / Index multiple criteria return multiple results | Excel Worksheet Functions | |||
Index lookup with multiple match criteria | Excel Worksheet Functions | |||
challenge! - match/index/lookup with multiple criteria | Excel Worksheet Functions | |||
Index & Match functions - multiple criteria and multiple results | Excel Worksheet Functions | |||
Index/Match Multiple Criteria | Excel Discussion (Misc queries) |