See attached workbook. Formula begins in cell F2 on down. Basically, it uses INDEX/MATCH to return a claim number from Group 2 where Group 1 has a date that falls between the Group 2 dates. It works fine to return one value, but, in instances of where I have multiple matching values, how do I return them both? (or even more)?
Can they be concatenated in the same cell separated by comma, or placed in the next cell over? Either way I am fine with. Thanks.
Using an array formula, it's easy to get them appearring side-by-side. Array Formulae are a little different from ordinary formulas in that they MUST be 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 the outside of your formula. If you do not CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.
=IFERROR(INDEX($P$2:$P$10, SMALL(IF($C2=$O$2:$O$10, ROW($O$2:$O$10)-MIN(ROW($O$2:$O$10))+1, ""), COLUMN(A1))),"")
Don't type the curly braces yourself - it won't work...
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
Actually, it only matched by ID. Need to also factor in date as well..(e..g Date in cell B2 is greater than or equal to value in $M$2:$M$4, less than or equal to $N$2:$M$4, assuming it matches with the ID).
Misread your post.... I'll take a look later on (away from PC for 5-6 hours.
I converted the date formats back from CUSTOM to DATE as this was causing Excel (and me!!) to get confused by the USA habit if using MM-DD-YYYY, whereas everyone else uses DD-MM-YYYY.
This array formula works as described:
=IFERROR(INDEX($P$2:$P$13,SMALL(IF(($O$2:$O$13=$C2)*($B2>=$M$2:$M$13)*($B2<=$N$2:$N$13),ROW($O$2:$O$13)),COLUMNS($A:A))-1),"")
Thanks for your hard work and follow-up!!! I appreciated it..
You're welcome and thanks for the Reputation. Sorry for misreading your post first time round, too.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks