Index match formula retunring first occurence

Hi, welcome to the forum

Originally Posted by Dinah_M

...I feel like there is an easy answer out there, but I'm just too "blind/tired" to see it...

Yes there is - that's the way Index/Match (and vlookup) works - it stops looking when it finds a match.

If you want to pull all occurrences of a certain SKU, you could probably use the index/small/if ARRAY function, but to offer better help, we would need to see a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

I have created a synopsis version of my WB, but am having issues uploading an .xlsx work book. It is under the KB limit. I have tried Manage Attachments, but always get a error notice: Upload of file failed.

I've turned off my firewall, and followed the instructions at the tutorial to a T (not that it is difficult to figure out), but it just won't accept the file.

Thank you for your time!

Many members don't like them.... but you could try a 3rd party file hosting site....

That said, sometimes I get teh same message, usually when I'm ravelling and using a flakey www connection. Try, try and try again.

I assume that its something like these instructions you're following:

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Maybe this upload will take.

Thanks again.

Try this.

1. I notice your formula does not look at the months, will there only ever be 1 entry/month?
2. If only 1 entry, try this...
=INDEX($D:$D,SMALL(IF($C$21:$C$43=$B$1,ROW($A$21:$A$43)),ROWS($A$1:A1)))
ARRAY entered ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Then copy down.

Happy to help, and you are not taking advantage of anything. We prefer to teach, rather than to just spoon-feed.

The attached site does a far better job of explaining how this all works...
https://fiveminutelessons.com/learn-...le-values-list

Slightly different but essentially the same.

Can you post a sample sheet,complete with more representative data AND your expected results?

Yeah I kinda figured you might have duplicates in any month lol

1. If you change the text dates to real dates in A4:A15, you can eliminate col B altogether, and do away with C4:C15. Just enter the 1st of the month (1/1/2018, 2/1/2018 etc) and set the format to MMM). Then let the ARRAY formula do the work for you.
2. Are you looking to SHOW all matching criteria, or SUM them?

to SHOW them all...
=IFERROR(INDEX($D:$D,SMALL(IF(($C$21:$C$53=$B$1)*($A$21:$A$53>=$A$4)*($A$21:$A$53<=EOMONTH($A$4,0)),ROW($A$21:$A$53)),ROWS($A$1:A1))),"")
CSE and then copy down

to SUM them all, use this regular formula
=SUMIFS($D$21:$D$53,$C$21:$C$53,$B$1,$A$21:$A$53,">=$"&$A4,$A$21:$A$53,"<="&EOMONTH($A$,0))
copied down

I added 2 more sets of Jan data to test with. See the attached file.

Happy to help and thanks for the kind words and feedback