I am trying to get different information from duplicate values. I am looking to get names from duplicate numbers based off of a master list of numbers. Here is the formula I have been using but it is not working correctly:
{=INDEX(B$2:B$3270,SMALL(IF(MATCH(J2,A$2:A$3270,0),ROW($A2:$A3270)),COUNTIF(J$2:J2,J2)))}
I attached the file as it is easier to see there.
Maybe this
=IFERROR(INDEX(B$2:B$3270,SMALL(IF($A$2:$A$3270=J2,ROW($A$2:$A$3270)-1),COUNTIF($J$2:J2,J2))),"")
...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. Press F2 on that cell and try again.
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
Hi All,
a possible alternative approach (for Excel 2010 or newer)
no need to be confirmed with control+shift+enter
Please Login or Register to view this content.
Regards
-----------------------------------------------------
At Excelforum, you can say "Thank you!" by clicking the star icon ("Add Reputation") below the post.
Please, mark your thread [SOLVED] if you received your answer.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks