Expiration Date Control

Brandt

New Member
Joined
Jun 23, 2009
Messages
20
Plan1

*ABCDE
1*****
2Lot Search****
3*****
4*****
5Product NameLot NºExp. Date**
6AAAAAAAA12345601/01/2015**
7AAAAAAAA12356801/10/2018**
8BBBBBBBBB55544415/04/2014**
9BBBBBBBBB85544315/07/2015**
10BBBBBBBBB75585516/07/2019**
11BBBBBBBBB44455220/08/2015**
12BBBBBBBBB41214418/04/2014**
13*****
14*****
15NOTE 1: *in cell D2 I put *=INDEX(A6:A12;MATCH(B2;B6:B12;0))****
16NOTE 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

*ABCDE
2Lot Search****
3*****
4*****
5Product NameLot NºExp. Date**
6AAAAAAAA12345601/01/2015**
7*12356801/10/2018**
8BBBBBBBBB55544415/04/2014**
9*85544315/07/2015**
10*75585516/07/2019**
11*44455220/08/2015**
12*41214418/04/2014**
13*****
14*****
15NOTE 1: *in cell D2 I put *=INDEX(A6:A12;MATCH(B2;B6:B12;0))****
16NOTE 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
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Brandt

New Member
Joined
Jun 23, 2009
Messages
20
Plan1

*ABCDE
1*****
2Lot Search****
3*****
4*****
5Product NameLot NºExp. Date**
6AAAAAAAA12345601/01/2015**
7AAAAAAAA12356801/10/2018**
8BBBBBBBBB55544415/04/2014**
9BBBBBBBBB85544315/07/2015**
10BBBBBBBBB75585516/07/2019**
11BBBBBBBBB44455220/08/2015**
12BBBBBBBBB41214418/04/2014**
13*****
14*****
15NOTE 1: *in cell D2 I put *=INDEX(A6:A12;MATCH(B2;B6:B12;0))****
16NOTE 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

*ABCDE
2Lot Search****
3*****
4*****
5Product NameLot NºExp. Date**
6AAAAAAAA12345601/01/2015**
7*12356801/10/2018**
8BBBBBBBBB55544415/04/2014**
9*85544315/07/2015**
10*75585516/07/2019**
11*44455220/08/2015**
12*41214418/04/2014**
13*****
14*****
15NOTE 1: *in cell D2 I put *=INDEX(A6:A12;MATCH(B2;B6:B12;0))****
16NOTE 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

Well-known Member
Joined
Mar 6, 2013
Messages
769
*ABCDE
1*****
2755855116/07/2019**
3*****
4*****
5Product NameLot NºExp. Date**
6AAAAAAAA1234561/1/2015**
7AAAAAAAA1235681/10/2018**
8BBBBBBBBB55544415/04/2014**
9BBBBBBBBB85544315/07/2015**
10BBBBBBBBB75585516/07/2019**
11BBBBBBBBB44455220/08/2015**
12BBBBBBBBB41214418/04/2014**
13*****
14*****
15N

<tbody>
</tbody>
 

Brandt

New Member
Joined
Jun 23, 2009
Messages
20
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,719
Messages
5,597,736
Members
414,170
Latest member
Mdm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top