Forum Discussion
Multiple logic test formula
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))
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))
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 🙂
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.
- 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.
- 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!
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.
- 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.
