Forum Discussion
Multiple logic test formula
Building a workbook to record winter road maintenance operations mileage for a dpw. One record for each truck. Each record must choose one of 9 routes and one of 3 activities. These choices are made by selecting from a pick list which is generated by data pre-entered in a table in a separate sheet. The table data includes the 3 activities (salting, plowing, salting and plowing) and the 9 different routes with their corresponding mileage. Routes contain roads that can be treated in one pass (road mile) and roads that need to be treated in both directions (lane miles) and therefore the table lists separate mileages for salting and plowing. I cannot figure out the correct formula that logic tests selections from 2 different pick lists (route, activity) and returns mileage from the appropriate column in the table based on the 2 pick list selections. I have tried the IF with nested OR formula to no avail.
If I followed all that you need, the attached file is an example of what would work. Essentially it's a two-dimensional table, with the different routes on the vertical dimension, the different activities on the horizontal, and the corresponding mileages in the interstices of those dimensions.
After you select the route and the activity, a "simple" INDEX and MATCH formula retrieves the mileage.
=INDEX(E5:G13,MATCH(C17,D5:D13,0),MATCH(C18,E4:G4,0))
- mathetesSilver Contributor
If I followed all that you need, the attached file is an example of what would work. Essentially it's a two-dimensional table, with the different routes on the vertical dimension, the different activities on the horizontal, and the corresponding mileages in the interstices of those dimensions.
After you select the route and the activity, a "simple" INDEX and MATCH formula retrieves the mileage.
=INDEX(E5:G13,MATCH(C17,D5:D13,0),MATCH(C18,E4:G4,0))
- GranSuptCopper ContributorThanks for taking the time to reply. I’ll give this a go. I ‘think’ I got it to work using IF with nested AND but my formula would be quite long and tedious to write (9 routes X3 possible activities per route = 27 logical tests). Your solution seems much simpler and easier. Thanks again for the help!
- mathetesSilver Contributor
I’ll give this a go. I ‘think’ I got it to work using IF with nested AND but my formula would be quite long and tedious to write (9 routes X3 possible activities per route = 27 logical tests). Your solution seems much simpler and easier.
Yes, covering multiple possible combinations in a deeply "nested" IF formula can indeed work, but it is awfully difficult to create in the first place, harder still for another person to understand, and next to impossible to maintain (as routes change, for example).
Using a table (or array of those 27 combinations) and one of the many forms of retrieval of information FROM a table achieves the same objectives and is far more readily understood. And as things change--routes get added or deleted, routes get expanded or contracted--it's simple to add other rows, change the numbers in them, and the same formula still works.
If you're not fully familiar with INDEX and MATCH, here's a good reference. You'll also find helpful videos on YouTube.
Thanks again for the help!
You're more than welcome.
- GranSuptCopper Contributor
mathetes ,
I have successfully set up the index match formula that you suggested. My problem now is that when I attempt to copy the formula to subsequent rows in the main sheet the formula changes values. Specifically, it is shifting the table row values up one number (next row in the table) for each row I paste to (or fill down to). I have tried everything I can think of to overcome this but it absolutely will not copy the formula in duplicate. In other words it is filling down in series instead of duplicate. Since the data in the table remains constant (i.e. the list if routes) I need the formula to hold consistent values for each new row in the main record. The only way I can accomplish what I am trying to do is to manually enter the formula in each new row.
I should add that I created 3 separate columns for 3 different routes to accommodate a single truck doing more than one route. I do not know if this is the cause of my complication.
I apologize if my explanation is a little convoluted. Not my strong suit 🙂
- mathetesSilver Contributor
It sounds entirely like what you need to do is make use of relative and absolute references.
And I'm going to guess that it's referring to the table in absolute terms that is needed. So that would be something like
=INDEX($E$5:$G$13,MATCH(C17,$D$5:$D$13,0),MATCH(C18,$E$4:$G$4,0))
which I've now done in the original sheet I created for you. You'll have to modify that to use whatever is appropriate for your table, but between that example and the references above, I'm confident you'll figure it out.