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