Using an If/Then formula with an Index and Match

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&current!$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!
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Maybe something like this:
This is an array formula and must be entered with CTRL-SHIFT-ENTER.
Drag formula down as needed.
Excel Workbook
ABCDEF
1Order #Part #DateOrder #Or4
2Or1Par15/1/2018Part #Par5
3Or2Par25/2/2018Date(s)5/5/2018
4Or1Par15/3/20185/7/2018
5Or4Par45/4/2018
6Or4Par55/5/2018
7Or6Par65/6/2018
8Or4Par55/7/2018
9Or8Par85/8/2018
10Or9Par95/9/2018
Sheet
 
Upvote 0

Forum statistics

Threads
1,214,599
Messages
6,120,448
Members
448,966
Latest member
DannyC96

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