Formula to lookup text and return when it first and last shows a value in the next column

[kthof]

Below is an example of the data that I'm trying to analyse and I need 2 formulas to look up a style code and find the week when it was first stocked (i.e. the inventory column first showed a value for that code) and the week it went out of stock (i.e. the inventory was 0). A couple points to note, I can't look for the first occurrence of the style code as the data shows several weeks with 0 inventory in the first few weeks. Also the inventory may go to 0 several times over the time period, I want to find the last time it went to 0.

The answers I'd be looking for in the below example is:
Style A1234 came into stock in week 2 and went out of stock in week 4.
Style B2345 came into stock in week 3 and went out of stock in week 5.

Style Code	Week	Inventory
A1234	1
A1234	2	200
A1234	3	100
A1234	4
A1234	5
B2345	1
B2345	2
B2345	3	50
B2345	4	10
B2345	5

<tbody>
</tbody>

Can anyone help please??

 

[Eric W]

Re: Help with a formula to lookup text and return when it first and last shows a value in the next column

Try:

	A	B	C	D	E	F	G
1	Style Code	Week	Inventory		Style Code	Came into stock	Went out of stock
2	A1234	1			A1234	2	4
3	A1234	2	200		B2345	3	5
4	A1234	3	100
5	A1234	4
6	A1234	5
7	B2345	1
8	B2345	2
9	B2345	3	50
10	B2345	4	10
11	B2345	5

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

Array Formulas Cell Formula F2 {=INDEX($B$2:$B$11,MATCH(1,IF(($A$2:$A$11=E2)*($C$2:$C$11>0),1,0),0))} G2 {=INDEX($B$2:$B$11,SMALL(IF(($A$2:$A$11=E2)*($C$2:$C$11=0)*(ROW($A$2:$A$11)>=LOOKUP(2,1/(($A$2:$A$11=E2)*($C$2:$C$11>0)),ROW($A$2:$A$11))),ROW($A$2:$A$11)-ROW($A$2)+1),1))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[kthof]

Re: Help with a formula to lookup text and return when it first and last shows a value in the next column

Thank you Eric, that worked perfectly

 

[bosco_yip]

Re: Help with a formula to lookup text and return when it first and last shows a value in the next column

Another option,

1] Using Eric's table layout in post #.2

2] In F2, non-array formula copied right to G2 and all copied down :

=INDEX($B$2:$B$11,AGGREGATE(14+(LEFT(F$1)="C"),6,ROW($B$2:$B$11)-ROW($B$1)/($A$2:$A$11=$E2)/($C$2:$C$11<>""),1)+(LEFT(F$1)="W"))

Regards
Bosco

 

[István Hirsch]

Re: Help with a formula to lookup text and return when it first and last shows a value in the next column

..Or give this a try:

Excel Workbook
	A	B	C	D	E	F	G
1	Style Code	Week	Inventory		Style Code	In	Out
2	A1234	1			A1234	2	4
3	A1234	2	200		B2345	3	5
4	A1234	3	100
5	A1234	4
6	A1234	5
7	B2345	1
8	B2345	2
9	B2345	3	50
10	B2345	4	10
11	B2345	5
Sheet1