IF Statement with multiple answers

%3CLINGO-SUB%20id%3D%22lingo-sub-288324%22%20slang%3D%22en-US%22%3EIF%20Statement%20with%20multiple%20answers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-288324%22%20slang%3D%22en-US%22%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3E4%3C%2FTD%3E%3CTD%3Ethis%20cell%20to%20print%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E4%3C%2FTD%3E%3CTD%3Eand%20this%20cell%20as%20well%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E4%3C%2FTD%3E%3CTD%3Eand%20this%20cell%20as%20well%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3EHI%20guys%2C%20I%20thought%20I%20had%20IF%20statements%20down%2C%20but%20this%20one%20is%20not%20working%20for%20me.%26nbsp%3B%20What%20I%20am%20trying%20to%20do%20in%20one%20cell%20%5Bwhere%20the%20answer%20needs%20to%20go%5Dis%20look%20at%20these%20three%20cells%20and%20if%20the%20cell%20on%20the%20left%20%3D4%20then%20print%20what%20is%20in%20the%20cell%20next%20to%20it.%26nbsp%3B%20So%20three%20statements%20in%20one%2C%20with%20all%20three%20answers%20printing%20if%20they%20are%20all%204%2C%26nbsp%3B%20if%20not%20then%20it%20returns%20nothing%20for%20that%20cell.%26nbsp%3B%20It%20only%20pulls%20the%20first%20one%20using%20this%20formula%3A%3C%2FP%3E%3CP%3E%22%3DIF(a1%3D4%2Cb1%2Cif(a2%3D4%2Cb2%2CIF(a3%3D4%2Cb3%2C%22%22)))%22%26nbsp%3B%20%26nbsp%3BI%20need%20it%20to%20pull%20all%20three%20b1%2Cb2%2Cb3%20because%20they%20all%20equal%204.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-288324%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-288619%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Statement%20with%20multiple%20answers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-288619%22%20slang%3D%22en-US%22%3E%3CP%3ELinda%2C%20it's%20possible%20but%20bit%20hard%20to%20automate%20selection%20of%20answers%20based%20on%20colour.%20You%20have%20some%20numbers%20in%20column%20F%2C%20better%20based%20on%20them.%20Let%20say%3C%2FP%3E%0A%3CP%3E%3CEM%3Eif%20any%20of%20b8%3Ab11%20is%20equal%20to%201%20then%20add%20answer%20where%20F8%3AF11%20is%20equal%20to%203%2C5%2C7%20or%2010%20depends%20on%20the%20position%20of%201%20in%20b8%3Ab11%2C%20else%20nothing%3C%2FEM%3E%3C%2FP%3E%0A%3CP%3Eand%20combine%20above%20for%20all%20groups.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%3E(B8%3D1)%2B(B14%3D1)%2B(B23%3D1)%20means%20you%20have%201%20at%20least%20in%20one%20of%20these%20cells%2C%20thus%20some%20answers%20from%20G%20picked-up%2C%20otherwise%20nothing%20is%20noted%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-288375%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Statement%20with%20multiple%20answers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-288375%22%20slang%3D%22en-US%22%3E%3CP%3EI%20pick%20all%20the%20lines%20that%20have%20red%20answers%2C%20just%20incase%20they%20pick%20that%20answer%2C%20but%20only%20the%20ones%20that%20are%20picked%20will%20the%20answers%20print.%20Does%20that%20make%20sense%3F%3C%2FP%3E%3CP%3Edo%20I%20need%20these%20three%20below%3F%3C%2FP%3E%3CP%3E(B8%3D1)%2B(B14%3D1)%2B(B23%3D1)%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-288364%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Statement%20with%20multiple%20answers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-288364%22%20slang%3D%22en-US%22%3E%3CP%3EI%20see.%20And%20how%20do%20you%20define%20which%20lines%20to%20pickup%3F%20In%20your%20formula%3C%2FP%3E%0A%3CPRE%3E%3DIF(B8%3D1%2CG8%2CIF(B14%3D1%2CG14%2CIF(B23%3D1%2CG23%2C%22Nothing%20noted%20in%20this%20section%22)))%3C%2FPRE%3E%0A%3CP%3Ethat%20is%20first%20line%20in%20first%20and%20second%20groups%20(B1%2C%20B14)%20and%20the%20last%20line%20in%20the%20third%20groups%20(G23).%20You'd%20like%20to%20combine%20positions%20manually%2C%20or%20we%20take%20all%20first%20positions%20where%201%2C%20into%20another%20cell%20all%20second%20positions%20with%201%2C%20etc.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20manually%20that%20could%20be%3C%2FP%3E%0A%3CPRE%3E%3DIF(%20(B8%3D1)%2B(B14%3D1)%2B(B23%3D1)%2C%20%20IF(B8%3D1%2CG8%20%26amp%3B%20%22%20%22%2C%20%22%22)%20%26amp%3B%20IF(B14%3D1%2CG14%20%26amp%3B%20%22%20%22%2C%22%22)%20%26amp%3B%20IF(B23%3D1%2CG23%2C%20%22%22)%2C%22Nothing%20noted%20in%20this%20section%22)%3C%2FPRE%3E%0A%3CP%3Eand%20in%20cell%20D25%20attached%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-288361%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Statement%20with%20multiple%20answers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-288361%22%20slang%3D%22en-US%22%3E%3CP%3EI%20actually%20see%20that%20you%20used%20a%20range%20in%20column%20B.%26nbsp%3B%20I%20actually%20need%20it%20to%20be%20just%20one%20line%2C%20as%20each%20line%20has%20a%20different%20response%20that%20goes%20into%20a%20different%20location%20on%20the%20form.%26nbsp%3B%20How%20would%20this%20look%20like%20based%20upon%20how%20I%20did%20the%20IF%20statements%3F%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-288357%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Statement%20with%20multiple%20answers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-288357%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Linda%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThank%20you%20for%20the%20file.%20Formula%20could%20be%3C%2FP%3E%0A%3CPRE%3E%3DINDEX(%24G%2414%3A%24G%2417%2CMATCH(1%2C%24B%2414%3A%24B%2417%2C0))%20%26amp%3B%20%22%20%22%20%26amp%3B%0A%20%20INDEX(%24G%2420%3A%24G%2423%2CMATCH(1%2C%24B%2420%3A%24B%2423%2C0))%20%26amp%3B%20%22%20%22%20%26amp%3B%0A%20%20INDEX(%24G%248%3A%24G%2411%2CMATCH(1%2C%24B%248%3A%24B%2411%2C0))%3C%2FPRE%3E%0A%3CP%3Eit's%20in%20D24%20of%20the%20attached%20file.%20No%20error%20handling%20assuming%20you%20always%201%2C%20but%20that%20easily%20could%20be%20added.%3C%2FP%3E%0A%3CP%3EMore%20important%20assumption%20you%20have%20only%20one%201%20in%20each%20block%20of%20B%20cells%2C%20otherwise%20formula%20doesn't%20work%20and%20we%20need%20another%20one.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-288338%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Statement%20with%20multiple%20answers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-288338%22%20slang%3D%22en-US%22%3E%3CP%3Esorry%20I%20wasn't%20sure%20where%20i%20needed%20to%20reply%2C%20so%20reply%20here%20as%20well.%3C%2FP%3E%3CP%3EMy%20issue%20is%20that%20my%20IF%20statement%20is%20only%20picking%20up%20the%20first%20answer%2C%20but%20I%20need%20to%20have%20it%20pick%20up%20multiple%20answers.%20In%20this%20case%20both%20that%20are%20highlighted%20in%20yellow%2C%20per%20the%20IF%20statement%20written.%26nbsp%3B%20You%20will%20see%20what%20I%20am%20doing%20when%20you%20look%20at%20the%20formula.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-288330%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Statement%20with%20multiple%20answers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-288330%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Linda%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECould%20you%20please%20attach%20small%20sample%20file%20with%20your%20data%20and%20desired%20calculations%20return%2C%20where%20and%20what%20it%20shall%20be%20(without%20any%20formulas)%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor
4this cell to print
4and this cell as well
4and this cell as well

HI guys, I thought I had IF statements down, but this one is not working for me.  What I am trying to do in one cell [where the answer needs to go]is look at these three cells and if the cell on the left =4 then print what is in the cell next to it.  So three statements in one, with all three answers printing if they are all 4,  if not then it returns nothing for that cell.  It only pulls the first one using this formula:

"=IF(a1=4,b1,if(a2=4,b2,IF(a3=4,b3,"")))"   I need it to pull all three b1,b2,b3 because they all equal 4.

8 Replies

Hi Linda,

 

Could you please attach small sample file with your data and desired calculations return, where and what it shall be (without any formulas)

sorry I wasn't sure where i needed to reply, so reply here as well.

My issue is that my IF statement is only picking up the first answer, but I need to have it pick up multiple answers. In this case both that are highlighted in yellow, per the IF statement written.  You will see what I am doing when you look at the formula.

 

Hi Linda,

 

Thank you for the file. Formula could be

=INDEX($G$14:$G$17,MATCH(1,$B$14:$B$17,0)) & " " &
  INDEX($G$20:$G$23,MATCH(1,$B$20:$B$23,0)) & " " &
  INDEX($G$8:$G$11,MATCH(1,$B$8:$B$11,0))

it's in D24 of the attached file. No error handling assuming you always 1, but that easily could be added.

More important assumption you have only one 1 in each block of B cells, otherwise formula doesn't work and we need another one.

I actually see that you used a range in column B.  I actually need it to be just one line, as each line has a different response that goes into a different location on the form.  How would this look like based upon how I did the IF statements?  

I see. And how do you define which lines to pickup? In your formula

=IF(B8=1,G8,IF(B14=1,G14,IF(B23=1,G23,"Nothing noted in this section")))

that is first line in first and second groups (B1, B14) and the last line in the third groups (G23). You'd like to combine positions manually, or we take all first positions where 1, into another cell all second positions with 1, etc.

 

If manually that could be

=IF( (B8=1)+(B14=1)+(B23=1),  IF(B8=1,G8 & " ", "") & IF(B14=1,G14 & " ","") & IF(B23=1,G23, ""),"Nothing noted in this section")

and in cell D25 attached

I pick all the lines that have red answers, just incase they pick that answer, but only the ones that are picked will the answers print. Does that make sense?

do I need these three below?

(B8=1)+(B14=1)+(B23=1) 

 

Linda, it's possible but bit hard to automate selection of answers based on colour. You have some numbers in column F, better based on them. Let say

if any of b8:b11 is equal to 1 then add answer where F8:F11 is equal to 3,5,7 or 10 depends on the position of 1 in b8:b11, else nothing

and combine above for all groups.

 

(B8=1)+(B14=1)+(B23=1) means you have 1 at least in one of these cells, thus some answers from G picked-up, otherwise nothing is noted

please use & sign between each arguments like: "=IF(a1=4,b1&if(a2=4,b2&IF(a3=4,b3,"")))
result will be all four answers in single cell----------------dattasmile