Search through columns for a specific result

%3CLINGO-SUB%20id%3D%22lingo-sub-2274965%22%20slang%3D%22en-US%22%3ESearch%20through%20columns%20for%20a%20specific%20result%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2274965%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20everyone%2C%3C%2FP%3E%3CP%3EI'm%20providing%20an%202-sheet%20excel%20workbook%20with%20the%20problem%20ahead.%20I%20need%20a%20formula%20that%20searches%20every%205%20columns%20for%20a%20value.%20IF%20the%20value%20is%20one%20from%20a%20range%200-3%20THEN%20return%20a%20specific%20result%20for%20each%20value.%200%3DNone%2C%201%3D%26lt%3B30%2C%20and%20so%20on.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20tried%20nesting%20IF%20statements%20for%20every%20value%20through%20every%20column%20individually%2C%20using%20a%20MATCH%20statement%2C%20and%20lastly%20I%20used%20a%20formula%20that%20I've%20tried%20in%20the%20past%20but%20only%20for%20returning%20just%20true%2Ffalse.%26nbsp%3B%20Which%20is%20%3DIF(SUMPRODUCT((MOD(COLUMN('BASE!'!A2%3AY2)-1%2C5)%3D0)*('BASE!'!A2%3AY2%26gt%3B%3D1))%2C%221-29%20minutes%22%2CIF(SUMPRODUCT((MOD(COLUMN('BASE!'!A2%3AY2)-1%2C5)%3D0)*('BASE!'!A2%3AY2%26gt%3B%3D2))%2C%221-24%20hours%22%2CIF(SUMPRODUCT((MOD(COLUMN('BASE!'!A2%3AY2)-1%2C5)%3D0)*('BASE!'!A2%3AY2%26gt%3B%3D3))%2C%221-7%20days%22)))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20check%20the%20second%20sheet%20you'll%20see%20that%20one%20cell%20from%20the%20needed%20column%20has%20a%202%20but%20doesn't%20return%20the%20set%20value.%20I%20appreciate%20any%20feedback%20on%20the%20matter.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBest%20regards%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2274965%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Contributor

Hello everyone,

I'm providing an 2-sheet excel workbook with the problem ahead. I need a formula that searches every 5 columns for a value. IF the value is one from a range 0-3 THEN return a specific result for each value. 0=None, 1=<30, and so on.

 

I've tried nesting IF statements for every value through every column individually, using a MATCH statement, and lastly I used a formula that I've tried in the past but only for returning just true/false.  Which is =IF(SUMPRODUCT((MOD(COLUMN('BASE!'!A2:Y2)-1,5)=0)*('BASE!'!A2:Y2>=1)),"1-29 minutes",IF(SUMPRODUCT((MOD(COLUMN('BASE!'!A2:Y2)-1,5)=0)*('BASE!'!A2:Y2>=2)),"1-24 hours",IF(SUMPRODUCT((MOD(COLUMN('BASE!'!A2:Y2)-1,5)=0)*('BASE!'!A2:Y2>=3)),"1-7 days")))

 

If you check the second sheet you'll see that one cell from the needed column has a 2 but doesn't return the set value. I appreciate any feedback on the matter.

 

Best regards

 

7 Replies

@ElmerOmero Perhaps reverse the order of the conditions?

 

=IF(SUMPRODUCT((MOD(COLUMN('BASE!'!A2:Y2)-1,5)=0)*('BASE!'!A2:Y2>=3)),"1-7 days",IF(SUMPRODUCT((MOD(COLUMN('BASE!'!A2:Y2)-1,5)=0)*('BASE!'!A2:Y2>=2)),"1-24 hours",IF(SUMPRODUCT((MOD(COLUMN('BASE!'!A2:Y2)-1,5)=0)*('BASE!'!A2:Y2>=1)),"1-29 minutes","None")))

Thanks, Hans. It worked. Could you elaborate on why reversing the order helped? I can't wrap my head around it.

@ElmerOmero 

The row with 2 also contained a 1. If the condition for >=1 comes before the condition for >=2, the formula returns the value intended for >=1 and looks no further. By reversing the order of the conditions, the formula returns the value for >=2 and doesn't evaluate the condition for >=1.

One more question Hans,
I moved the set of values to the AA2:AY2 range of the workbook as I'm using it on a similar location on other excel doc I'm working with, but now the results don't correspond to the ones obtained whiles using the A2:Y2 range. Do you have any idea why this is happening?

@ElmerOmero 

The original formula uses MOD(COLUMN('BASE!'!A2:Y2)-1,5)=0. This condition is TRUE for columns 1, 6, 11, ... (i.e. A, F, K, ...). For example, the column number of column A is 1, so COLUMN(...)-1 = 0, and MOD(COLUMN(...)-1,5) = 0.

But for column AA, the column number is 27, so COLUMN(...)-1 = 26, and MOD(COLUMN(...)-1,5) = 1.

So you should change the formula to

 

=IF(SUMPRODUCT((MOD(COLUMN('BASE!'!AA2:AY2)-1,5)=1)*('BASE!'!AA2:AY2>=3)),"1-7 days",IF(SUMPRODUCT((MOD(COLUMN('BASE!'!AA2:AY2)-1,5)=1)*('BASE!'!AA2:AY2>=2)),"1-24 hours",IF(SUMPRODUCT((MOD(COLUMN('BASE!'!AA2:AY2)-1,5)=1)*('BASE!'!AA2:AY2>=1)),"1-29 minutes","None")))

@Hans VogelaarThat one is on me for not explain myself properly. I moved the data while correcting the ranges in the whole formula but didn't obtain the same results as if they were located at the A2:Y2 range. As soon as I moved everything back, I got the expected results again.

 

Thank you for your patience, though. I'm just trying to make sense out of it.

@ElmerOmero 

Not only do you need to change the range references, but also the value to compare MOD(...) to.