kylefoley76
Well-known Member
- Joined
- Mar 1, 2010
- Messages
- 1,553
I need a formula that can produce column D. Essentially, column B equals column A. So using column C I should be able to produce D.
I've got the macro in the vba and it's still not working.
maybe i'm not understanding the direction, control shift enter and copy down correctly. i have a mac if that means anything but i doubt it. i press control shift and enter but how can i copy down after that?
no, that doesn't do it...
Edit the formula, then use Command+Return to enter it as an array formula.
Hi Kylefoley76,This formula only works with 2 numbers in column c. Sometimes I might have up to 5 or 6 numbers in column c.
=SUBSTITUTE(SMALL(INDEX(ISNUMBER(SEARCH(","&$B$2:$B$7,","&C2))*$A$2:$A$7,),1)&
IFERROR(","&SMALL(INDEX(ISNUMBER(SEARCH(","&$B$2:$B$7,","&C2))*$A$2:$A$7,),2),"")&
IFERROR(","&SMALL(INDEX(ISNUMBER(SEARCH(","&$B$2:$B$7,","&C2))*$A$2:$A$7,),3),"")&
IFERROR(","&SMALL(INDEX(ISNUMBER(SEARCH(","&$B$2:$B$7,","&C2))*$A$2:$A$7,),4),"")&
IFERROR(","&SMALL(INDEX(ISNUMBER(SEARCH(","&$B$2:$B$7,","&C2))*$A$2:$A$7,),5),"")&
IFERROR(","&SMALL(INDEX(ISNUMBER(SEARCH(","&$B$2:$B$7,","&C2))*$A$2:$A$7,),6),"")&
IFERROR(","&SMALL(INDEX(ISNUMBER(SEARCH(","&$B$2:$B$7,","&C2))*$A$2:$A$7,),7),"")&
IFERROR(","&SMALL(INDEX(ISNUMBER(SEARCH(","&$B$2:$B$7,","&C2))*$A$2:$A$7,),8),""),"0,","")
Array formula - use Ctrl+Shift+Enter and not only Enter to enter the formula
=IFERROR(SMALL(IF(ISNUMBER(SEARCH(","&$B$2:$B$7,","&C2)),$A$2:$A$7),1),"")&
IFERROR(","&SMALL(IF(ISNUMBER(SEARCH(","&$B$2:$B$7,","&C2)),$A$2:$A$7),2),"")&
IFERROR(","&SMALL(IF(ISNUMBER(SEARCH(","&$B$2:$B$7,","&C2)),$A$2:$A$7),3),"")&
IFERROR(","&SMALL(IF(ISNUMBER(SEARCH(","&$B$2:$B$7,","&C2)),$A$2:$A$7),4),"")&
IFERROR(","&SMALL(IF(ISNUMBER(SEARCH(","&$B$2:$B$7,","&C2)),$A$2:$A$7),5),"")&
IFERROR(","&SMALL(IF(ISNUMBER(SEARCH(","&$B$2:$B$7,","&C2)),$A$2:$A$7),6),"")&
IFERROR(","&SMALL(IF(ISNUMBER(SEARCH(","&$B$2:$B$7,","&C2)),$A$2:$A$7),7),"")&
IFERROR(","&SMALL(IF(ISNUMBER(SEARCH(","&$B$2:$B$7,","&C2)),$A$2:$A$7),8),"")