Trying to use Index-Match with criteria and between dates

[arossijr]

Hello All!!
I have been searching the forum, came across similar problems and various formulas and I for the life of me cannot get them to work.
I have used Index and Match and IFError in formulas and they are great. I donot think that Vlookup will work unless I arrange the Dates ...
I have 2 Tables.
Table1 has the Data I am trying to retrieve.
The Headers are: Store#, Invoice#,InvDate,PackSlip,Ven,Part#,Cost, CoreCost,TotalCost,Credit Type,Notes

table2 has the Reference Material and Fields for Retrieved Data
The Headers are:Close Date,Search Date, Store#,Vendor,Line,Part#, PInv#,P InvDate,P PckSlp,CreditAmount$

The first part of data that i am trying to retrieve is PInv#. But I need to Match the Part# during a Date Range. The "Start Date" would be the Close Date and then "End Date" would be Search Date. the field that would be checked would be InvDate on Table1

I have tried using array and standard and I am lost...

Any and all kicks in the right direction is greatly appreciated!!

 

[Marcelo Branco]

Marcelo,

Is was my part number field, some of the numbers where not "numbers" I had to convert them via the green arrow in the left of the cell....

Ok, but it's also important to check if the dates are real dates (numbers).

M.

 

[arossijr]

Ok, but it's also important to check if the dates are real dates (numbers).

M.

Just did that to be on the safe side this report is a slippery slide to begin with!!! Cant tell you how much i appreciate the Help and correction of the formula!!!
I have see the @ and double brackets before... But when i try to use them it doesnt always work...

 

[Marcelo Branco]

Glad to help

M.

 

[arossijr]

I was hoping that this Array Formula was 100% but it does not appear to be... For some reason it has decided to Grab data when the 1 date is less than the date it should be...
=IFERROR(INDEX(PrimeInv[Invoice '#],MATCH(1,(PrimeInv[Inv Date]>=[@[Close Date]])*(PrimeInv[Inv Date]<=[@[Search Date]])*(PrimeInv[Part '#]=[@[Part'#]]),0)),"No Data Entered")

It pulls the data, just for some reason it gets to a point and it grabs the wrong date. All the Date Fields are matched for Formatting...

I was so excited that it worked i built the other formulas around it then found this issue... Any Ideas? Suggestions?

 

[Marcelo Branco]

It pulls the data, just for some reason it gets to a point and it grabs the wrong date. All the Date Fields are matched for Formatting...

I was so excited that it worked i built the other formulas around it then found this issue... Any Ideas? Suggestions?

Formatting a cell as a date does not guarantee that the cell contains a real date (number). You need to check.
Assuming the dates are, for example in A2: A100, in an empty column try
= ISNUMBER (A2)
And copy down.
If any result is FALSE this indicates that the cell in question does not contain a real date.

M.

 

[arossijr]

Formatting a cell as a date does not guarantee that the cell contains a real date (number). You need to check.
Assuming the dates are, for example in A2: A100, in an empty column try
= ISNUMBER (A2)
And copy down.
If any result is FALSE this indicates that the cell in question does not contain a real date.




M.

Just double checked all the Dates, the come up as True in all the tables... See it is pulling Data just that is is pulling Data that is out of the Date Range...Ex If it is 6/5/18 for the Start Date and 8/18/18 for the end date it is pulling 4/05/18 because the Part Number Matches... The Part Number is only there to complete the Verification and to make sure that it was returned on a date in between the Start and End dates...

 

[arossijr]

So here lies the dilema how do I do a Index Match when the Dates are not exact but fall into a date range?
Can IF be used after Match?

 

[Marcelo Branco]

Worked perfectly for me

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

1​

Store#​

Invoice#​

InvDate​

PackSlip​

Ven​

Part#​

Cost​

CoreCost​

TotalCost​

Credit​

Type​

Notes​

2​

1​

04/05/2018​

p1​

3​

1​

04/05/2018​

p2​

4​

1​

04/05/2018​

p3​

5​

2​

06/08/2018​

p1​

6​

2​

06/08/2018​

p2​

7​

2​

06/08/2018​

p3​

8​

9​

10​

Close Date​

Search Date​

Store#​

Vendor​

Line​

Part#​

PInv#​

P InvDate​

P PckSlp​

CreditAmount$​

11​

06/05/2018​

08/18/2018​

p2​

2​

12​

04/03/2018​

08/18/2018​

p1​

1​

13​

07/02/2018​

08/18/2018​

p3​

No Data Entered​

14​

Table PrimeInv in A1:L7

Table 2 in A10:J13

Array formula in G11
=IFERROR(INDEX(PrimeInv[Invoice'#],MATCH(1,(PrimeInv[InvDate]>=[@[Close Date]])*([@[Search Date]]>=PrimeInv[InvDate])*(PrimeInv[Part'#]=[@[Part'#]]),0)),"No Data Entered")
Ctrl+Shift+Enter

M.

 

[arossijr]

Worked perfectly for me

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

1​

Store#​

Invoice#​

InvDate​

PackSlip​

Ven​

Part#​

Cost​

CoreCost​

TotalCost​

Credit​

Type​

Notes​

2​

1​

04/05/2018​

p1​

3​

1​

04/05/2018​

p2​

4​

1​

04/05/2018​

p3​

5​

2​

06/08/2018​

p1​

6​

2​

06/08/2018​

p2​

7​

2​

06/08/2018​

p3​

8​

9​

10​

Close Date​

Search Date​

Store#​

Vendor​

Line​

Part#​

PInv#​

P InvDate​

P PckSlp​

CreditAmount$​

11​

06/05/2018​

08/18/2018​

p2​

2​

12​

04/03/2018​

08/18/2018​

p1​

1​

13​

07/02/2018​

08/18/2018​

p3​

No Data Entered​

14​

<tbody>
</tbody>

Table PrimeInv in A1:L7

Table 2 in A10:J13

Array formula in G11
=IFERROR(INDEX(PrimeInv[Invoice'#],MATCH(1,(PrimeInv[InvDate]>=[@[Close Date]])*([@[Search Date]=]=>=PrimeInv[InvDate])*(PrimeInv[Part'#]=[@[Part'#]]),0)),"No Data Entered")
Ctrl+Shift+Enter

M.

Marcelo,

Thank You again for the Fast Reply and a working formula... I had Prime Invoice Date before the Search Date...
I am getting much better results now....

Dumb question... Once the Formula finds an answer, and it comes across another Part # that is the same but different Prime Inv Date but falls in same Close and Search date is there a way to skip and go to next result?

Thanks again!
Tony

 

[arossijr]

Marcelo, and Anyone else

Marcelo's formula works, But I am still getting errant problems with data...
I applied some more data to the Prime Table, verified that the dates are dates, numbers are numbers etc...
I am getting returns of data well before the Close Date Range and or Search Date Range... It returns an Invoice which is coming up in the Range, so luckily it is getting caught by the Packing Slip Formula...
I have not but I am about to try and sort the Data by the Close Date on the Manifest table and Invoice Date on the Prime Table....
I cant figure out how they are getting caught in the loop