• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sumproduct or choose

Hi ,

Can you upload the results for Vitamin 24 ? Suppose all the date data remains the same , what will be the expected output for Vitamin 24 ?

Narayan
 
the very simple answer of this query by Index-Match function:

=IFERROR(INDEX(Permanent!$B$3:$K$14,MATCH('Master sheet'!$A4,Permanent!$A$3:$A$14,0),MATCH('Master sheet'!B$3,Permanent!$B$2:$K$2,0)),INDEX(
 
Nikunj's formula (in post #.29) is a incomplete formula :

=IFERROR(INDEX(Permanent!$B$3:$K$14,MATCH('Master sheet'!$A4,Permanent!$A$3:$A$14,0),MATCH('Master sheet'!B$3,Permanent!$B$2:$K$2,0)),INDEX(

You better wait for his reply

Regards
Bosco
 
bosco_yip said:
The new requirement need a helper column to adjust the Date Lookup Range, the revision as in :

1] Helper column, M3 enter :

=A4 and Custom Cell Format >> "Helper";;;

2] Helper column, M4 formula copy down :

=IF(ISNUMBER(MATCH(1,INDEX((Permanent!$A$3:$A$14=$A4)*(Permanent!$B$3:$B$14=$B4)+(Temporary!$A$3:$A$14=$A4)*(Temporary!$B$3:$B$14=$B4),0),0)),A4,M3)

3] In C4, formula copy across and down :

=IFERROR(1/(1/INDEX(Permanent!$C$3:$L$14,MATCH(1,INDEX((Permanent!$A$3:$A$14=$M4)*(Permanent!$B$3:$B$14=$B4),0),0),MATCH(C$3,Permanent!$C$2:$L$2,0))),IFERROR(1/(1/INDEX(Temporary!$C$3:$L$14,MATCH(1,INDEX((Temporary!$A$3:$A$14=$M4)*(Temporary!$B$3:$B$14=$B4),0),0),MATCH(C$3,Temporary!$C$2:$L$2,0))),""))

Regards
Bosco
Click to expand...

Dear Bosco

As you suggested with helper column, i have added and i didnot get the value in Row 7,8,9 and 11,12.

Please find the attached copy.
 

Attachments

  • 2 Ways Lookup Bosco.xlsx
    15.5 KB · Views: 1
Anbuselvam K said:
Dear Bosco

As you suggested with helper column, i have added and i didnot get the value in Row 7,8,9 and 11,12.

Please find the attached copy.
Click to expand...

upload_2017-5-12_16-35-59.png

1] Row 5,6,7 and 9,10 return blank, because :

In B4 =Vitamin 29, B5:B7 =Vitamin 12, they are not match

And, B8 =Vitamin 19, B9:B10 =Vitamin 12, they are not match

2] However, Row 3 return value, because B2 =Vitamin 12, B3 =Vitamin 12, they are match.

Regards
 
Dear Bosco

In the master sheet, we are entering production dates and different product names from Temporary and permanent sheet formulation.

For example,

we may produce one-day vitamin 12, Vitamin 19 and Vitamin 29 and we may not produce all the above 3 products for 10 days. In that 10 days, we may produce Vitamin 13, 15 and 16.

So the formulation should extract data from Permanent and temporary sheets as per the master sheet dates and product names repeatedly and when required.
 
Anbuselvam K said:
Dear Bosco

In the master sheet, we are entering production dates and different product names from Temporary and permanent sheet formulation.

For example,

we may produce one-day vitamin 12, Vitamin 19 and Vitamin 29 and we may not produce all the above 3 products for 10 days. In that 10 days, we may produce Vitamin 13, 15 and 16.

So the formulation should extract data from Permanent and temporary sheets as per the master sheet dates and product names repeatedly and when required.
Click to expand...

Hi,

My formula appear does not meet with your requirement, please ignore of my suggested formula.

Regards
Bosco
 
You must log in or register to reply here.
Back
Top