Trying to use Index-Match with criteria and between dates

arossijr

Board Regular
Joined
Aug 29, 2016
Messages
72
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!!
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Example of Formula that I am trying to use(Please note Headers are different but essential the same)
Code:
=INDEX(PrimeInv[Invoice '#],MATCH(1,(PrimeInv[Inv Date]>=[Close Date])*(PrimeInv[Inv Date]<=[Search Date])*(PrimeInv[Part '#]=[Part'#]),0))[CODE/]
 
Upvote 0
Hard to help without a data sample for testing purposes.
Anyway see if this works
=INDEX(PrimeInv[Invoice'#],MATCH(1,(PrimeInv[InvDate]>=[@[Close Date]])*(PrimeInv[InvDate]<=[@[Search Date]])*(PrimeInv[Part'#]=[@[Part'#]]),0))
Ctrl+Shift+Enter

M.
 
Upvote 0
Have you tried the formula in post 3?

M.

Yes, we must have been cross posting... I used the formula that you gave me and entered it as an array. I have a number of #NA , but that is the least of my worries... Thank you Marcelo Branco!!!
I can send a scrubbed version and more detail if that would be helpful...

The Search Date may cause me a little headache in matching data... I only have Table for the one table for June... the other table with the Close Date and Search Date is from Jan1 2018 to current...
If i wanted to add another Criteria I assume that i can add it at the end? or before the Part# part of the match? I want to match Line on Table 2 with Vendor On Table 1.. just to make sure that if a part # is in 2 lines that it grabs the correct data...
 
Upvote 0
Marcelo Branco, Quick Question... Formula is working but its not catching everything... I have a data entry that is on Prime Table but since it has a date of 6/18/2018 the formula does not pick it up... How can i correct that?
 
Upvote 0
Marcelo Branco, Quick Question... Formula is working but its not catching everything... I have a data entry that is on Prime Table but since it has a date of 6/18/2018 the formula does not pick it up... How can i correct that?

Marcelo, Disregard!! I found the issue for this part... some of the Part Numbers where not set as a Number... oops...
 
Upvote 0
Marcelo Branco, Quick Question... Formula is working but its not catching everything... I have a data entry that is on Prime Table but since it has a date of 6/18/2018 the formula does not pick it up... How can i correct that?

Check if the dates are real dates (numbers), not text.

M.
 
Upvote 0
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....
 
Upvote 0

Forum statistics

Threads
1,212,936
Messages
6,110,764
Members
448,297
Latest member
cocolasticot50

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
Back
Top