Expiration Date Control

[Brandt]

Plan1

*	A	B	C	D	E
1	*	*	*	*	*
2	Lot Search	*	*	*	*
3	*	*	*	*	*
4	*	*	*	*	*
5	Product Name	Lot Nº	Exp. Date	*	*
6	AAAAAAAA	123456	01/01/2015	*	*
7	AAAAAAAA	123568	01/10/2018	*	*
8	BBBBBBBBB	555444	15/04/2014	*	*
9	BBBBBBBBB	855443	15/07/2015	*	*
10	BBBBBBBBB	755855	16/07/2019	*	*
11	BBBBBBBBB	444552	20/08/2015	*	*
12	BBBBBBBBB	412144	18/04/2014	*	*
13	*	*	*	*	*
14	*	*	*	*	*
15	NOTE 1: *in cell D2 I put *=INDEX(A6:A12;MATCH(B2;B6:B12;0))	*	*	*	*
16	NOTE 2: *in cell E2 I put *=INDEX(C6:C12;MATCH(D2;C6:C12;0))	*	*	*	*

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4


Hi,

I put two formulas to return product name and expiration date from a list of products but there's a problem. Many products have two or more lot numbers and formula stops when first lot is found... What I want is put a lot number in cell B2 and in cells D2 and E2 to obtain product name and its expiration date.

I tried to write data another way:

Plan1

*	A	B	C	D	E
2	Lot Search	*	*	*	*
3	*	*	*	*	*
4	*	*	*	*	*
5	Product Name	Lot Nº	Exp. Date	*	*
6	AAAAAAAA	123456	01/01/2015	*	*
7	*	123568	01/10/2018	*	*
8	BBBBBBBBB	555444	15/04/2014	*	*
9	*	855443	15/07/2015	*	*
10	*	755855	16/07/2019	*	*
11	*	444552	20/08/2015	*	*
12	*	412144	18/04/2014	*	*
13	*	*	*	*	*
14	*	*	*	*	*
15	NOTE 1: *in cell D2 I put *=INDEX(A6:A12;MATCH(B2;B6:B12;0))	*	*	*	*
16	NOTE 2: *in cell E2 I put *=INDEX(C6:C12;MATCH(D2;C6:C12;0))	*	*	*	*

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:129px;"><col style="width:82px;"><col style="width:85px;"><col style="width:135px;"><col style="width:94px;"></colgroup><tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

but it doesn't work too.



Thanks

Fabio

 

[Brandt]

Plan1

*	A	B	C	D	E
1	*	*	*	*	*
2	Lot Search	*	*	*	*
3	*	*	*	*	*
4	*	*	*	*	*
5	Product Name	Lot Nº	Exp. Date	*	*
6	AAAAAAAA	123456	01/01/2015	*	*
7	AAAAAAAA	123568	01/10/2018	*	*
8	BBBBBBBBB	555444	15/04/2014	*	*
9	BBBBBBBBB	855443	15/07/2015	*	*
10	BBBBBBBBB	755855	16/07/2019	*	*
11	BBBBBBBBB	444552	20/08/2015	*	*
12	BBBBBBBBB	412144	18/04/2014	*	*
13	*	*	*	*	*
14	*	*	*	*	*
15	NOTE 1: *in cell D2 I put *=INDEX(A6:A12;MATCH(B2;B6:B12;0))	*	*	*	*
16	NOTE 2: *in cell E2 I put *=INDEX(C6:C12;MATCH(D2;C6:C12;0))	*	*	*	*

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4


Hi,

I put two formulas to return product name and expiration date from a list of products but there's a problem. Many products have two or more lot numbers and formula stops when first lot is found... What I want is put a lot number in cell B2 and in cells D2 and E2 to obtain product name and its expiration date.

I tried to write data another way:

Plan1

*	A	B	C	D	E
2	Lot Search	*	*	*	*
3	*	*	*	*	*
4	*	*	*	*	*
5	Product Name	Lot Nº	Exp. Date	*	*
6	AAAAAAAA	123456	01/01/2015	*	*
7	*	123568	01/10/2018	*	*
8	BBBBBBBBB	555444	15/04/2014	*	*
9	*	855443	15/07/2015	*	*
10	*	755855	16/07/2019	*	*
11	*	444552	20/08/2015	*	*
12	*	412144	18/04/2014	*	*
13	*	*	*	*	*
14	*	*	*	*	*
15	NOTE 1: *in cell D2 I put *=INDEX(A6:A12;MATCH(B2;B6:B12;0))	*	*	*	*
16	NOTE 2: *in cell E2 I put *=INDEX(C6:C12;MATCH(D2;C6:C12;0))	*	*	*	*

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

but it doesn't work too.



Thanks

Fabio

Well, maybe I wasn't clear in my question. So, let me try this way (sorry my English skills...)

In cell B2 I want to write a lot number (for example 123456) and I wish a function that looks in column A for product name that match this lot. In another cell (for example C2) I wish a function that returns expiration data based in these last informations i.e lot number and product name found. I've tried functions like you can see in the end of table but for products with two or more lot numbers it doesnt work (returns only the first name product and expiration date...)

Thanks.

 

[jamtay317]

try this
=INDEX($c$8:$c$18,MATCH(b2,$b$8:$b$18,0))

 

[jamtay317]

*	A	B	C	D	E
1	*	*	*	*	*
2	755855	1	16/07/2019	*	*
3	*	*	*	*	*
4	*	*	*	*	*
5	Product Name	Lot Nº	Exp. Date	*	*
6	AAAAAAAA	123456	1/1/2015	*	*
7	AAAAAAAA	123568	1/10/2018	*	*
8	BBBBBBBBB	555444	15/04/2014	*	*
9	BBBBBBBBB	855443	15/07/2015	*	*
10	BBBBBBBBB	755855	16/07/2019	*	*
11	BBBBBBBBB	444552	20/08/2015	*	*
12	BBBBBBBBB	412144	18/04/2014	*	*
13	*	*	*	*	*
14	*	*	*	*	*
15	N

<tbody>
</tbody>

 

[Brandt]

try this
=INDEX($c$8:$c$18,MATCH(b2,$b$8:$b$18,0))

This function works until first lot number match. For example, if a product has two or more lot numbers, this function stops at first result. I need check product WITH exact lot number. Let's suppose I need to find product AAA. This product has three lot numbers: 123, 456 and 789. The function must to search in column A for "product AAA". After, the function must to check column B for given lot number (that was specified in cell "lot search"), let's suppose I want to find lot 456 of this product. So, in a cell called "search result" the function returns " AAA 456 25/06/2014 as result.

Thanks

Fábio Brandt