Search through columns for a specific result

Copper 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.