Multiple logic test formula

[GranSupt]

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.

[#2 · Accepted solution]

@GranSupt 

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))

[#3]

Thanks 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!

[#4]

@GranSupt 

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.

[#5]

The ability to change routes was a concern I had. Using the INDEX and MATCH command almost effortlessly facilitates changes. I am a novice at Excel, self learning as I go. One thing I have discovered is there are several ways to skin a cat so to speak. It will take me some time to learn the different strategies. I’ve barely scratched the surface and am grateful for folks like yourself that take the time to share their expertise.

[#6]

@GranSupt 

I am a novice at Excel, self learning as I go. One thing I have discovered is there are several ways to skin a cat so to speak. It will take me some time to learn the different strategies.

Self-taught is a good thing; it's basically how I learned the ins and outs of spreadsheets. I had the advantage of doing so back in the '80s and '90s when Lotus and then Microsoft issued their software on discs accompanied with printed manuals. I would read the manuals periodically, from start to finish; inevitably noticed what you comment on--that there were multiple ways to get from point A to point B.

I would in particular encourage you to learn to think and design with tables or datasets at the heart of your design. Excel excels in various methods of slicing and dicing, summarizing salient data, from a transactional database. For example, if you were to ultimately want to know how many times per month each route was salted, plowed, or both, a simple daily record of each route and activity could be easily processed by means of the Pivot Table.....and Excel would be doing ALL of the hard work.

I’ve barely scratched the surface and am grateful for folks like yourself that take the time to share their expertise.

I know (virtually) some of the other folks who answer questions here in the forum; I think we all voluntarily do this because we enjoy helping others, appreciating folks like you who also want to learn, and--I know this is true for me--I keep learning more by taking the time to answer a question like yours,

[#7]

@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 :)

 

[#8]

@GranSupt 

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.

[#9]

It believe you understand my issue correctly and through research and trial and error I already experimented using absolute references in the formula and it did not work solve my problem. But I may have had something wrong. I will re-try using your formula as a guide and see what happens. Thank you.

[#10]

This worked perfectly. When I initially tried it I was placing the “$” in front of the column value only instead of both the column and row values.
Thank you again for the help.

[#11]

Again, you're very welcome.  The absolute reference notion is flexibly absolute: i.e., it can be totally referring always to the single cell or range, as you needed to do in the case of the table; but you can lock it to a row or a column as well, but leave the column or row, respectively, "relative". That anchoring flexibility is sometimes useful as well.