Currently using a Index/Small array formula to return matches to a lookup value. See attached spreadsheet.
One issue I would like to correct is that I would like to only return unique values, not duplicates (ones that have already been returned in formula) In example, number 5 has the name Eric listed twice in source, and returns it twice in formula (formula in "Before" sheet, cells B2 to D6). Looking to have it returned only once, as shown in sheet "After". Any ideas? Thanks.
It would be more helpful to show us the results you want, really. What are Index1, Index2 and Index3 and how do they relate to the source data?
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Actually, Sheet "After" does show the results I want...Index 1, 2, and 3 is just the formula copied over horizontally to show any matches based on the number.
Formula is in the "Before" sheet...
Last edited by Brawnystaff; 01-08-2017 at 01:53 PM.
Try this...
Data Range
This array formula** entered in B2:
=IFERROR(INDEX($I$2:$I$9,MATCH(0,IF($H$2:$H$9=$A2,COUNTIF($A$2:A2,$I$2:$I$9)),0)),"")
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
Copy across to D2 then down as needed.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Thanks, Tony...Had seen that Countif addition in an earlier posting, just wasn't sure how to integrate it into my example.
Needs a slight tweak...
To:=IFERROR(INDEX($I$2:$I$9,MATCH(0,IF($H$2:$H$9=$A2,COUNTIF($A$2:A2,$I$2:$I$9)),0)),"")
=IFERROR(INDEX($I$2:$I$9,MATCH(0,IF($H$2:$H$9=$A2,COUNTIF($A2:A2,$I$2:$I$9)),0)),"")
Still array entered.
You're welcome. Thanks for the feedback!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks