Extract Conditional Data

[Graemea]

Hi,

I have sample sales data as in A1:AZ7 in the screenshot below.

I would like to create a formula that, for each Product Code, returns the country associated with a value that isn't "-" and is closest in position to the last column in the range.

In my example, Germany would be extracted against AAA and Italy for BBB.

Can someone please suggest a formula to achieve this?

Thanks!

PS the dates in row 1 are text, not numbers

 

[mse330]

Hi Graemea,

Try the below ...

Test.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Product	Product	Region	Country	31-Dec-15	31-Jan-16	29-Feb-16	31-Mar-16	30-Apr-16	31-May-16	30-Jun-16	31-Jul-16	31-Aug-16	30-Sep-16	31-Oct-16
2	AAA	Apple	Europe	UK	-	100	100	100	100	100	100	100	100	-	-
3	AAA	Apple	Europe	France	-	-	100	100	100	100	100	100	100	-	-
4	AAA	Apple	Europe	Germany	-	-	-	100	100	100	100	100	100	100	-
5	BBB	Banana	Europe	Spain	-	-	100	100	100	100	100	100	-	-	-
6	BBB	Banana	Europe	Italy	-	-	-	100	100	100	100	100	100	-	-
7	BBB	Banana	Europe	Portugal	-	100	100	100	100	100	100	100	-	-	-
8	CCC	Cucumber	Europe	Switzerland	-	-	-	100	-	-	-	-	-	-	-
9	CCC	Cucumber	Europe	Netherland	-	-	-	100	100	100	-	-	-	-	-
10	CCC	Cucumber	Europe	Austria	-	-	100	100	100	-	-	-	-	-	-
11
12
13	AAA	Germany
14	BBB	Italy
15	CCC	Netherland
Sheet1

Cell Formulas
Range	Formula
F1:O1	F1	=EOMONTH(E1,1)
B13:B15	B13	=INDEX($D$1:$D$10,LARGE(IF($A$2:$A$10=A13,IF(COLUMN($A$2:$O$10)=LARGE(IF($A$2:$A$10=A13,IF($A$2:$O$10<>"-",COLUMN($A$2:$O$10),0),0),1),IF($A$2:$O$10<>"-",ROW($A$2:$A$10),0),0),0),1))

 

[Graemea]

That's awesome, thank you!

A last quick question, if i may: does this not need to be an array function (i.e. with Ctrl-Alt-Delete)?

 

[mse330]

It should work without CSE as Index & Large functions accept arrays. I have office 365 it works as expected without CSE. Which office version you have ? Have you tried it? Does it work or not ?