Range Value formula

[Vignesh Veerasamy]

Dear Gurus,

Below is the sample data that will be downloaded from my ERP. User request is as below.

I would like to know the formula to bring the data as shown below.
Particular data which is between a range of data against the material.

Kindly help,Thanks in advance.

 

[chirayu]

Try this. Not sure if correct.

 

[Hui]

To allow for just the Date:
=VLOOKUP(C3,H3:J6,3)

To allow for just the Material and Date:
=INDEX(J3:J6,SUMPRODUCT((G3:G6=B3)*(H3:H6<=C3)*(I3:I6>=C3)*(ROW(G3:G6)-2)))

 

[Deepak]

Do the advance filter

 

[Hui]

3 Very different and all correct solutions!

 

[Deepak]

one more!

=LOOKUP(2,1/(($H$3:$H$6<=C3)*($G$3:$G$6=B3)),$J$3:$J$6)

but i am still not sure about the goal.

 

[Deepak]

If #6 is correct then this one is more versatile.

=LOOKUP(2,1/(($H$3:$H$6<=C3)*($I$3:$I$6>=C3)*($G$3:$G$6=B3)),$J$3:$J$6)

 

[Vignesh Veerasamy]

Dear Hui / Deepak,
You are awesome.Many Thanks.

My one doubt is if in case if I have a list of materials in column B & set of data in between columns G:J same can be used?

To allow for just the Material and Date:
=INDEX(J3:J6,SUMPRODUCT((G3:G6=B3)*(H3:H6<=C3)*(I3:I6>=C3)*(ROW(G3:G6)-2)))

Also please clarify why "-2"is used in formula?

 

[Deepak]

Hi, Just change the range based on your search criteria!

 

[Vignesh Veerasamy]

Hi, Just change the range based on your search criteria!

Dear Deepak,

Thank you.
I also tried Advanced filter option but it shows error message saying
The extract field has an invalid field.

Will the filter fill the field column D3 automatically?

PS : I am new to advance filter.first time user of advanced filter.

 

[Vignesh Veerasamy]

Try this. Not sure if correct.

Thank you chirayu.

 

[Hui]

Because the first row of data is 3 not 1

 

[Vignesh Veerasamy]

Because the first row of data is 3 not 1

Dear Hui,

Thank You.

 

[Vignesh Veerasamy]

Dear All,

I tried applying the formula but observed the required output is not shown.
I have attached my working file kindly let know where I went wrong.
Sample case shown in brown were data need to be as FY is it showing as FX.

Kindly help.

Vignesh V

 

[Hui]

The formula for H319 should be:
=INDEX($M$2:$M$721,SUMPRODUCT(($J$2:$J$721=F319)*($K$2:$K$721<=G319)*($L$2:$L$721>=G319)*(ROW($J$2:$J$721)-1)))
Copy up and down

Because your data table starts in Row 2 not row 3 as per the previous example

 

[Vignesh Veerasamy]

The formula for H319 should be:
=INDEX($M$2:$M$721,SUMPRODUCT(($J$2:$J$721=F319)*($K$2:$K$721<=G319)*($L$2:$L$721>=G319)*(ROW($J$2:$J$721)-1)))
Copy up and down

Because your data table starts in Row 2 not row 3 as per the previous example

Dear Hui,
Greetings & Many Thanks.
One question is that when there is no data in the field why does formula call the data and show? screen shot attached as below.

 

[Hui]

Because you never specified that was an option and so it wasn't allowed for

Just add a test

H16:
=IF(F16<>"",INDEX($M$2:$M$721,SUMPRODUCT(($J$2:$J$721=F16)*($K$2:$K$721<=G16)*($L$2:$L$721>=G16)*(ROW($J$2:$J$721)-1))),"")

Copy down

 

[Vignesh Veerasamy]

Because you never specified that was an option and so it wasn't allowed for

Just add a test

H16:
=IF(F16<>"",INDEX($M$2:$M$721,SUMPRODUCT(($J$2:$J$721=F16)*($K$2:$K$721<=G16)*($L$2:$L$721>=G16)*(ROW($J$2:$J$721)-1))),"")

Copy down

Dear Hui,

Greetings & Thank you.
I pasted the formula.
But getting below error.Inbetween data along with blanks is shown as shown in screen shot.

 

[Vignesh Veerasamy]

Because you never specified that was an option and so it wasn't allowed for

Just add a test

H16:
=IF(F16<>"",INDEX($M$2:$M$721,SUMPRODUCT(($J$2:$J$721=F16)*($K$2:$K$721<=G16)*($L$2:$L$721>=G16)*(ROW($J$2:$J$721)-1))),"")

Copy down

 

[Hui]

The formula =IF(F16<>"",INDEX($M$2:$M$721,SUMPRODUCT(($J$2:$J$721=F16)*($K$2:$K$721<=G16)*($L$2:$L$721>=G16)*(ROW($J$2:$J$721)-1))),"")
will only work down to Row 721
I guess you have more rows than that
Change 721 to suit

 

[Hui]

Please post files with Ranges highlighted
Pictures mean nothing

 

[Vignesh Veerasamy]

Please post files with Ranges highlighted
Pictures mean nothing

Dear Hui,

I have attached the file.
Range of data where the content need to be looked in to is shown in yellow
and the output is required in column H comparing the data in column F.

Requirement is date against the model shown in column F should be compared with data available in columns J K L M

 

[Deepak]

Updated File??

 

[Vignesh Veerasamy]

Dear Deepak,

Sorry missed to attach the file.

 

[Hui]

H16:
=IF(LEN(F16)>1,INDEX($M$2:$M$721,SUMPRODUCT(($J$2:$J$721=F16)*($K$2:$K$721<=G16)*($L$2:$L$721>=G16)*(ROW($J$2:$J$721)-1))),"")
Copy down

Because your formula in Column F leaves a Space " " not ""