How Index, Match return a value depends on earlier, current and further date?

[Anbuselvam]

Dear All,

Please find the excel sheet which is having Master sheet, permanent and teporary sheets.

Master sheet is output sheet

Temporary and permanent are my product formulation sheet.

I need to extract formulations from those two sheets to master sheet with a contions of Product name and creation date of product formulation.

For getting the results, i have some formulation and i did not get desired value.

Please check the attachments and do the needfull.

Note: In master sheet i have added expected results too for your study.

Sincerely yours
Anbuselvam K

Attachments:

Permanent

Date	Product	Incredient 1	Incredient 2	Incredient 4	Incredient 5	Incredient 6	Incredient 7	Incredient 8	Incredient 10	Incredient 11	Incredient 12
19-Mar-15	Vitamin 12		13.13%	2.99%	19.91%		0.01%	0.09%
19-Mar-15	Vitamin 1	66.52%		3.00%	30.36%		0.02%	0.10%
27-Jun-15	Vitamin 2	56.93%	17.86%	3.22%	21.78%	0.08%	0.02%	0.10%
19-Mar-15	Vitamin 3	7.46%	86.60%	2.97%	2.97%		0.01%
19-Mar-15	Vitamin 4	10.23%	89.77%				0.00%
19-Mar-15	Vitamin 5	18.63%	73.62%	3.95%	3.80%
19-Mar-15	Vitamin 6	10.95%	81.24%	3.46%	4.35%
19-Mar-15	Vitamin 7	7.74%	85.26%	3.00%	4.00%		0.00%
19-Mar-15	Vitamin 8	5.00%	94.60%			0.40%
19-Mar-15	Vitamin 9	12.16%	80.08%	3.37%	4.39%		0.00%
19-Mar-15	Vitamin 10	8.10%	83.59%	2.97%	5.34%
19-Mar-15	Vitamin 11								96.65%	2.75%	0.60%

<tbody>
</tbody>

Temporary

Date	Product	Incredient 1	Incredient 2	Incredient 4	Incredient 5	Incredient 6	Incredient 7	Incredient 8	Incredient 10	Incredient 11	Incredient 12
2-May-16	Vitamin 12	63.87%	13.13%	1.00%	22.00%		6.00%	0.09%
2-May-15	Vitamin 100	66.52%		3.00%	30.36%		0.02%	0.10%
2-May-15	Vitamin 101	56.93%	17.86%	3.22%	21.78%	0.08%	0.02%	0.10%
2-May-15	Vitamin 102	7.46%	86.60%	2.97%	2.97%		0.01%
2-May-15	Vitamin 103	10.23%	89.77%				0.00%
2-May-15	Vitamin 104	18.63%	73.62%	3.95%	3.80%
2-May-15	Vitamin 105	10.95%	81.24%	3.46%	4.35%
2-May-15	Vitamin 106	7.74%	85.26%	3.00%	4.00%		0.00%
2-May-15	Vitamin 107	5.00%	94.60%			0.40%
2-May-15	Vitamin 108	12.16%	80.08%	3.37%	4.39%		0.00%
2-May-15	Vitamin 109					100.00%
2-May-15	Vitamin 110								96.65%	2.75%	0.60%

<tbody>
</tbody>

Master Sheet

Date	Product	Incredient 1	Incredient 2	Incredient 4	Incredient 5	Incredient 6	Incredient 7	Incredient 8	Incredient 10	Incredient 11	Incredient 12
19-Mar-13	Vitamin 12	66.52%	0.00%	3.00%	30.36%	0.00%	0.02%	0.10%	0.00%	0.00%	0.00%
19-Mar-15	Vitamin 12	56.93%	17.86%	3.22%	21.78%	0.08%	0.02%	0.10%	0.00%	0.00%	0.00%
23-Mar-15	Vitamin 12	7.46%	86.60%	2.97%	2.97%	0.00%	0.01%	0.00%	0.00%	0.00%	0.00%
19-Mar-16	Vitamin 12	10.23%	89.77%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%
21-Mar-16	Vitamin 12	18.63%	73.62%	3.95%	3.80%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%
1-May-16	Vitamin 12	10.95%	81.24%	3.46%	4.35%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%
5-May-17	Vitamin 12	7.74%	85.26%	3.00%	4.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%
21-May-17	Vitamin 12	5.00%	94.60%	0.00%	0.00%	0.40%	0.00%	0.00%	0.00%	0.00%	0.00%
23-May-17	Vitamin 12	12.16%	80.08%	3.37%	4.39%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%

<tbody>
</tbody>

Master Sheet C2 formula is

=IF($P4 = 0, INDEX(Permanent!$C$3:$L$14, $O4, MATCH(C$3, Permanent!$C$2:$L$2, 0)), INDEX(Temporary!$C$3:$L$14, $P4, MATCH(C$3, Temporary!$C$2:$L$2, 0)))

Across and down.

Expected Results in Master Sheet

Date	Product	Incredient 1	Incredient 2	Incredient 4	Incredient 5	Incredient 6	Incredient 7	Incredient 8	Incredient 10	Incredient 11	Incredient 12
19-Mar-13	Vitamin 12		0.13127	0.02987	0.19914		0.0001	0.0009
19-Mar-15	Vitamin 12		0.13127	0.02987	0.19914		0.0001	0.0009
23-Mar-15	Vitamin 12		0.13127	0.02987	0.19914		0.0001	0.0009
19-Mar-16	Vitamin 12		0.13127	0.02987	0.19914		0.0001	0.0009
21-Mar-16	Vitamin 12		0.13127	0.02987	0.19914		0.0001	0.0009
1-May-16	Vitamin 12		0.13127	0.02987	0.19914		0.0001	0.0009
5-May-17	Vitamin 12	0.63872	0.13127	0.01	0.22		0.06	0.0009
21-May-17	Vitamin 12	0.63872	0.13127	0.01	0.22		0.06	0.0009
23-May-17	Vitamin 12	0.63872	0.13127	0.01	0.22		0.06	0.0009

<tbody>
</tbody>

 

[oldbrewer]

on 19th March 2013 there is no Vitamin 12 so where do you get the ingredients from ?

 

[oldbrewer]

ahh - should it say 2015 ?

 

[oldbrewer]

what is temporary sheet for ?

 

[Anbuselvam]

on 19th March 2013 there is no Vitamin 12 so where do you get the ingredients from ?

It should take the value from next available date which is 19-Mar-2015.



what is temporary sheet for?

I have formulations in permanent and temporary which are used by regularly and rarely?

 

[Anbuselvam]

Can anyone help me out?

 

[oldbrewer]

permanent
Date	Product	Incredient 1	Incredient 2	Incredient 4	Incredient 5	Incredient 6	Incredient 7	Incredient 8	Incredient 10	Incredient 11	Incredient 12
19-Mar-13	Vitamin 12		13.13%	2.99%	19.91%		0.01%	0.09%
Temporary
Date	Product	Incredient 1	Incredient 2	Incredient 4	Incredient 5	Incredient 6	Incredient 7	Incredient 8	Incredient 10	Incredient 11	Incredient 12
02-May-16	Vitamin 12	63.87%	13.13%	1.00%	22.00%		6.00%	0.09%
Master Sheet
Date	Product	Incredient 1	Incredient 2	Incredient 4	Incredient 5	Incredient 6	Incredient 7	Incredient 8	Incredient 10	Incredient 11	Incredient 12
19-Mar-13	Vitamin 12	66.52%	0.00%	3.00%	30.36%	0.00%	0.02%	0.10%	0.00%	0.00%	0.00%
19-Mar-15	Vitamin 12	56.93%	17.86%	3.22%	21.78%	0.08%	0.02%	0.10%	0.00%	0.00%	0.00%
23-Mar-15	Vitamin 12	7.46%	86.60%	2.97%	2.97%	0.00%	0.01%	0.00%	0.00%	0.00%	0.00%
19-Mar-16	Vitamin 12	10.23%	89.77%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%
21-Mar-16	Vitamin 12	18.63%	73.62%	3.95%	3.80%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%
01-May-16	Vitamin 12	10.95%	81.24%	3.46%	4.35%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%
05-May-17	Vitamin 12	7.74%	85.26%	3.00%	4.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%
21-May-17	Vitamin 12	5.00%	94.60%	0.00%	0.00%	0.40%	0.00%	0.00%	0.00%	0.00%	0.00%
23-May-17	Vitamin 12	12.16%	80.08%	3.37%	4.39%	0.00%	0.00%	0.00%	0.00%	0.00%	0
Expected Results in Master Sheet
Date	Product	Incredient 1	Incredient 2	Incredient 4	Incredient 5	Incredient 6	Incredient 7	Incredient 8	Incredient 10	Incredient 11	Incredient 12
19-Mar-13	Vitamin 12		0.13127	0.02987	0.19914		0.0001	0.0009
19-Mar-15	Vitamin 12		0.13127	0.02987	0.19914		0.0001	0.0009
23-Mar-15	Vitamin 12		0.13127	0.02987	0.19914		0.0001	0.0009
19-Mar-16	Vitamin 12		0.13127	0.02987	0.19914		0.0001	0.0009
21-Mar-16	Vitamin 12		0.13127	0.02987	0.19914		0.0001	0.0009
01-May-16	Vitamin 12		0.13127	0.02987	0.19914		0.0001	0.0009
05-May-17	Vitamin 12	0.63872	0.13127	0.01	0.22		0.06	0.0009
21-May-17	Vitamin 12	0.63872	0.13127	0.01	0.22		0.06	0.0009
23-May-17	Vitamin 12	0.63872	0.13127	0.01	0.22		0.06	0.0009
you have only one row of vitamin12 data in permanent and temporary - so how do you get 9 rows in master sheet
and your expected results are identical for 6 rows - how did they change on 5 may 2017
I am not at all clear (yet) what you want
all I can see is - the vitamin12 formulation was unchanged from 19 march 13 but changed in temporary on 2 may 16
and master sheet shows changes on 8 dates
your expected results are CONSTANT until 5 may 2017
Please try and explain what this data means and what you want from it…

<colgroup><col><col><col><col><col><col><col><col><col><col span="4"></colgroup><tbody>
</tbody>

 

[Anbuselvam]

you have only one row of vitamin12 data in permanent and temporary - so how do you get 9 rows in master sheet?

The one row is the date of creation of Vitamin 12 in the permanent sheet by 19-Mar-13 and the next change in formulation in Temporary sheet on 02-May-2016

In the master sheet date is production date.

we did so many productions from 19-Mar-2013 on different dates by using that formulation from permanent sheet up to 02-May-2016, as the next changes in the Vitamin 12 in a temporary sheet on 02-May-2016.

As like Vitamin 12, we have 15 formulations and each formulation will change time to time. According to the changes by date wise, i need to extract the data from permanent and temporary sheets (Ingredients 1 to 12).