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