Index & Match with more than 2 conditions

[sanjuss2]

Hi
I am using data in excel 2010 where I want to extract data from table on the basis of more than 2 conditions to be fulfilled
for eq.
A B C D E F G
1 Product Codes
2 Date Branch SB CD KBT DDTY KTY
3 05-08-2015 KNG 12 20 16 19 15
4 05-08-2015 MAL 28 36 32 35 31
5 09-08-2015 BRL 45 53 49 52 48
6 12-08-2015 GOL 33 41 37 40 36
7 05-08-2015 BRL 14 22 18 21 17
8 06-08-2015 KNG 8 16 12 15 11
9 09-08-2015 MAL 46 54 50 53 49
If I am using criteria date : 05/08/2015 Branch : MAL Product Code : KBT
the result must be : 32

Please give some formula I have used index with formula but it can be used only for 2 conditions which is available on net....

[MarvinP]

Hi Sanjuss,

Find attached the answer to this question. It is a CSE formula (or Array) formula that needs to be confirmed with a Control+Shift+Enter keystroke. If you don't want to use this you can build a Helper Column and concatenate your Date and Branch into a single cell and do an Index Match using it. If you want to see other ways to get this answer, keep this thread alive and keep asking.

[sanjuss2]

It's Gr8 & work fine

Thank you very much Marvin......

[Tony Valko]

Here's another one...

Data Range

	A	B	C	D	E	F	G
1
2	Date	Branch	SB	CD	KBT	DDTY	KTY
3	8/5/2015	KNG	12	20	16	19	15
4	8/5/2015	MAL	28	36	32	35	31
5	8/9/2015	BRL	45	53	49	52	48
6	8/12/2015	GOL	33	41	37	40	36
7	8/5/2015	BRL	14	22	18	21	17
8	8/6/2015	KNG	8	16	12	15	11
9	8/9/2015	MAL	46	54	50	53	49
10	------	------	------	------	------	------	------
11
12	8/5/2015	MAL	KBT	32

This formula entered in D12:

=SUMIFS(INDEX(C3:G9,0,MATCH(C12,C2:G2,0)),A3:A9,A12,B3:B9,B12)

[Czeslaw]

Two more options.
Another formula.

Please Login or Register  to view this content.

[sanjuss2]

Thank you
Tony Valko & Czeslaw ....
The formulas are really gr8 & excellents.

Czeslaw
I have question what is 14; 6 in AGGREGATE function?

[MarvinP]

Hey sanjuss2,

Here are the arguments for the Aggregate function. It looks like 14, 6 means Large , Product.

http://www.exceluser.com/excel_help/...-aggregate.htm

[Tony Valko]

You're welcome. Thanks for the feedback!


If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.