Hello,
I have this array formula that looks is suppose to look in cell A12 and copy down all the potential results from B2:B8. The formula find the first results but gives me errors for the rest of the results. Would someone please take a look at this formula?
{=IF(ISERROR(INDEX($A$1:$B$8,SMALL(IF($A$1:$A$8=$A$12,ROW($A$1:$A$8)),ROW($1:$1)),2)),"",INDEX($A$1:$B$8,SMALL(IF($A$1:$A$8=$A$12,ROW($A1:$A$8)),ROW($1:$1)),2))}
Hi,
Your profile and attachment indicate that you can use:
=IFERROR(INDEX($A$1:$B$8,SMALL(IF($A$1:$A$8=$A$12,ROW($A$1:$A$8)-MIN(ROW($A$1:$A$8))+1),ROWS($1:1)),2),"")
Regards
Thanks XOR LX. Works now
One last question. If my reference data is on a tab named Data would my formula look like this. Also where is the solved button
=IFERROR(INDEX(Data!A2:B8,SMALL(IF($A$1:$A$8=$A$12,ROW($A$1:$A$8)-MIN(ROW($A$1:$A$8))+1),ROWS($1:1)),2),"")
No. It needs to be an absolute reference (the dollar signs) to begin with. And you'd need to change more than just that range (and you appear to be starting in row 2 now):Originally Posted by tennisbuck
=IFERROR(INDEX(Data!$A$2:$B$8,SMALL(IF(Data!$A$2:$A$8=$A$12,ROW(Data!$A$2:$A$8)-MIN(ROW(Data!$A$2:$A$8))+1),ROWS($1:1)),2),"")
Regards
Thanks for all the help!
You're welcome!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks