Forum Discussion
Search through columns for a specific result
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.
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.
