ChantellMaree
New Member
- Joined
- May 22, 2018
- Messages
- 1
Alright, so I'm pretty new to excel and am trying to create a formula to save me some time.
Basically I have to make sure our orders are on time. I get a spreadsheet each week with information from where we ordered our parts from, and I have to compare the dates they gave me last week, with the ones I have this week to see if there is a variance.
The problem that I'm having is that I'm doing Index and Match with an Array.
So I have it look for both the order number and the part that was ordered to return back to me the date in which is listed.
BUT. There are some instances that there are duplicate order number and part numbers, so it just returns back to me the first date it finds instead of all of them.
I was told that I need to have an IF/Then function to basically say, IF there are duplicate order numbers/part number combo then return all the dates.
I believe I also need to use a count, but I have no idea how to do this or where to put it in the formula.
The current formula I have is: =INDEX('last week'!$G$2:$G$169,MATCH(current!$A$2¤t!$B$2,'last week'!$A$2:$A$169&'last week'!$B$2:$B$169,0))
Any help would be appreciated, and if I need to clarify I can.
Thank you!
Basically I have to make sure our orders are on time. I get a spreadsheet each week with information from where we ordered our parts from, and I have to compare the dates they gave me last week, with the ones I have this week to see if there is a variance.
The problem that I'm having is that I'm doing Index and Match with an Array.
So I have it look for both the order number and the part that was ordered to return back to me the date in which is listed.
BUT. There are some instances that there are duplicate order number and part numbers, so it just returns back to me the first date it finds instead of all of them.
I was told that I need to have an IF/Then function to basically say, IF there are duplicate order numbers/part number combo then return all the dates.
I believe I also need to use a count, but I have no idea how to do this or where to put it in the formula.
The current formula I have is: =INDEX('last week'!$G$2:$G$169,MATCH(current!$A$2¤t!$B$2,'last week'!$A$2:$A$169&'last week'!$B$2:$B$169,0))
Any help would be appreciated, and if I need to clarify I can.
Thank you!