Forum Discussion
Solved
Formula Excel
I have a formula that can spot a word in a Cell and add a letter to a text in another cell. ie if cell A3 shows WT and cell B3 shows Card then change A3 to WTC.... What I'm need to do now is have a fo...
Norman_Glenn Ah, now I see the formula you are using. Basically you can just repeat the calculation you do to find "CARDBOARD" and use it for "GOOD":
=B19&IF(ISNUMBER(SEARCH("CARDBOARD",C19))*((B19="FS")+(B19="MT")+(B19="JS")+(B19="HS")),"C","")&IF(ISNUMBER(SEARCH("GOOD",C19))*((B19="FS")+(B19="MT")+(B19="JS")+(B19="HS")),"G","")That said, this formula also checks and only appends "C" if the cell in column B is one of the following: FS, MT, JS, or HS. I just copied that IF() condition exactly and only changed the SEARCH to be for "GOOD". My question is if appending a "G" should also be only for those cases. If so you could make it easier to read and slightly more efficient:
=B19&IF((B19="FS")+(B19="MT")+(B19="JS")+(B19="HS"), IF(ISNUMBER(SEARCH("CARDBOARD",C19)),"C","") &IF(ISNUMBER(SEARCH("GOOD",C19)),"G","") ,"")Also right now it will append a "C" if appropriate and THEN a "G" if appropriate (i.e. in some case it appends "CG". I believe that is your intent.
I don't see any text manipulation in that formula. i see you subtracting 2 numbers based on INDEX() of 2 arrays. That said, you are looking up if $A20=... and then INDEX based on the location of $A20 in the array $A$20:$A$60. I get that you are probably copying down so $A20 increments down $A21, $A22, etc... but why do you need the MATCH part? Can't you just use $W20 and $S20 instead of those INDEX(MATCH())? Instead of just the MATCH condition you could use ROW($A1) and as that copies down it will increment accordingly to give you 1,2,3.... or as a more generic solution (that allows you to copy it to rows < 20 you can use ROW($A20)-ROW($A$19) and if you HAVE to copy down to row 1 you can use ROW($A20) - ROW($A$20) + 1.
Here is the attachment