INDEX MATCH MATCH Lookup

[Graemea]

Hi,


I have data in the range A1:E1000, with the following fields:


A2:E2 - Headings for columns, respectively Product, Date, Colour, Sales and Sales


The Sales columns above can be distinguished from one another by the text "Country1" in cell D1 and "Country2" in cell E1.


From the above data I would like to use an INDEX-MATCH-MATCH formula to extract the Sales for a given Product, Date, Colour and for Country2.


So, for example, it would answer the question what were the sales in country 2 of product AAA in red on 31/08/2017.


Can someone please suggest a solution?


Thanks!

 

[Aladin Akyurek]

Control+shif+enter, not just enter:

=INDEX($D$3:$E$100,MATCH(1,IF($A$3:$A$100="AAA",IF($B$3:$B$100="31/08/2017"+0,IF($C$3:$C$100="red",1))),0),MATCH("country2",$D$1:$E$1,0))

 

[Marcelo Branco]

Try SUMIFS

Something like
=SUMIFS(E$3:E$100,A$3:A$100,"AAA",B$3:B$100,DATE(2017,8,31),C$3:C$100,"red")

or to get the country column
=SUMIFS(INDEX(D$3:E$100,0,MATCH("Country2",D$1:E$1,0)),A$3:A$100,"AAA",B$3:B$100,DATE(2017,8,31),C$3:C$100,"red")

Of course you can use cell references instead of hard code the criteria.

M.

 

[Graemea]

Thanks very much!

 

[Aladin Akyurek]

Thanks very much!

You are welcome.