Returning data from a matrix table using multiple criteria

[dsmith1088]

Hi there

New to the forum, hoping you can help! I have numerous matrix tables showing miles per gallon (which varies with mileage and tonnage). Each vehicle registration number has its own table. I want to return values from those tables based on criteria. See below.

As you can see, the mileage and tonnage fall within the ranges within the table. I need to select the MPG falling underneath the higher range.


Note, there are too many tables to code the formula to each individual table, I need the table selection to be formulated/automated. Also, i can play about with the format/layout/position of tables if needed.

Thank you

David

 

[oldbrewer]

your image failed - show us a typical table (highlight it, copy, paste here)

 

[dsmith1088]

Hi there

Thanks for the update. See below. Is there any way to post a screenshot - would be much clearer for you to see

[TABLE="width: 410"]
<colgroup><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Vehicle reg[/TD]
[TD]XYZ 5000[/TD]
[TD]User enterred[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mileage[/TD]
[TD="align: right"]150[/TD]
[TD]User enterred[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tonnage[/TD]
[TD="align: right"]2.4[/TD]
[TD]User enterred[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MPG[/TD]
[TD] [/TD]
[TD]To be calculatedCalculated[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]XYZ 5000[/TD]
[TD] [/TD]
[TD="colspan: 2"]Mileage[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]200[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Tonnage[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]XYZ 1000[/TD]
[TD] [/TD]
[TD="colspan: 2"]Mileage[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]300[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Tonnage[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]4[/TD]
[/TR]
</tbody>[/TABLE]

David

 

[oldbrewer]

[TABLE="width: 1461"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col span="10"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]lorry 1[/TD]
[TD][/TD]
[TD]tonnage[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]mpg[/TD]
[TD]miles[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]table[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]15.5[/TD]
[TD="align: right"]15.2[/TD]
[TD="align: right"]14.9[/TD]
[TD="align: right"]14.6[/TD]
[TD="align: right"]14.3[/TD]
[TD="align: right"]14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]col L[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]col S[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]16.4[/TD]
[TD="align: right"]16.1[/TD]
[TD="align: right"]15.8[/TD]
[TD="align: right"]15.5[/TD]
[TD="align: right"]15.2[/TD]
[TD="align: right"]14.9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]17.3[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]16.7[/TD]
[TD="align: right"]16.4[/TD]
[TD="align: right"]16.1[/TD]
[TD="align: right"]15.8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]125[/TD]
[TD="align: right"]18.2[/TD]
[TD="align: right"]17.9[/TD]
[TD="align: right"]17.6[/TD]
[TD="align: right"]17.3[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]16.7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]19.1[/TD]
[TD="align: right"]18.8[/TD]
[TD="align: right"]18.5[/TD]
[TD="align: right"]18.2[/TD]
[TD="align: right"]17.9[/TD]
[TD="align: right"]17.6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]175[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]19.7[/TD]
[TD="align: right"]19.4[/TD]
[TD="align: right"]19.1[/TD]
[TD="align: right"]18.8[/TD]
[TD="align: right"]18.5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]20.9[/TD]
[TD="align: right"]20.6[/TD]
[TD="align: right"]20.3[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]19.7[/TD]
[TD="align: right"]19.4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]choose[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]225[/TD]
[TD="align: right"]21.8[/TD]
[TD="align: right"]21.5[/TD]
[TD="align: right"]21.2[/TD]
[TD="align: right"]20.9[/TD]
[TD="align: right"]20.6[/TD]
[TD="align: right"]20.3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]lorry[/TD]
[TD]tonnage[/TD]
[TD]mileage[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]250[/TD]
[TD="align: right"]22.7[/TD]
[TD="align: right"]22.4[/TD]
[TD="align: right"]22.1[/TD]
[TD="align: right"]21.8[/TD]
[TD="align: right"]21.5[/TD]
[TD="align: right"]21.2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]275[/TD]
[TD="align: right"]23.6[/TD]
[TD="align: right"]23.3[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]22.7[/TD]
[TD="align: right"]22.4[/TD]
[TD="align: right"]22.1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]lorry 2[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]225[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]24.5[/TD]
[TD="align: right"]24.2[/TD]
[TD="align: right"]23.9[/TD]
[TD="align: right"]23.6[/TD]
[TD="align: right"]23.3[/TD]
[TD="align: right"]23[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]row 16[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD]lorry 2[/TD]
[TD][/TD]
[TD]tonnage[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD]mpg[/TD]
[TD]miles[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD]table[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]15.7[/TD]
[TD="align: right"]15.5[/TD]
[TD="align: right"]15.2[/TD]
[TD="align: right"]14.9[/TD]
[TD="align: right"]14.6[/TD]
[TD="align: right"]14.3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]lorry 2[/TD]
[TD][/TD]
[TD]tonnage[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]15.8[/TD]
[TD="align: right"]15.5[/TD]
[TD="align: right"]15.2[/TD]
[TD="align: right"]14.9[/TD]
[TD="align: right"]14.6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]mpg[/TD]
[TD]miles[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]30[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]16.7[/TD]
[TD="align: right"]16.5[/TD]
[TD="align: right"]16.2[/TD]
[TD="align: right"]15.9[/TD]
[TD="align: right"]15.6[/TD]
[TD="align: right"]15.3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]table[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]15.7[/TD]
[TD="align: right"]15.5[/TD]
[TD="align: right"]15.2[/TD]
[TD="align: right"]14.9[/TD]
[TD="align: right"]14.6[/TD]
[TD="align: right"]14.3[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD="align: right"]125[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]16.8[/TD]
[TD="align: right"]16.5[/TD]
[TD="align: right"]16.2[/TD]
[TD="align: right"]15.9[/TD]
[TD="align: right"]15.6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]15.8[/TD]
[TD="align: right"]15.5[/TD]
[TD="align: right"]15.2[/TD]
[TD="align: right"]14.9[/TD]
[TD="align: right"]14.6[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]17.7[/TD]
[TD="align: right"]17.5[/TD]
[TD="align: right"]17.2[/TD]
[TD="align: right"]16.9[/TD]
[TD="align: right"]16.6[/TD]
[TD="align: right"]16.3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]16.7[/TD]
[TD="align: right"]16.5[/TD]
[TD="align: right"]16.2[/TD]
[TD="align: right"]15.9[/TD]
[TD="align: right"]15.6[/TD]
[TD="align: right"]15.3[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD="align: right"]175[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]17.8[/TD]
[TD="align: right"]17.5[/TD]
[TD="align: right"]17.2[/TD]
[TD="align: right"]16.9[/TD]
[TD="align: right"]16.6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]125[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]16.8[/TD]
[TD="align: right"]16.5[/TD]
[TD="align: right"]16.2[/TD]
[TD="align: right"]15.9[/TD]
[TD="align: right"]15.6[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]18.7[/TD]
[TD="align: right"]18.5[/TD]
[TD="align: right"]18.2[/TD]
[TD="align: right"]17.9[/TD]
[TD="align: right"]17.6[/TD]
[TD="align: right"]17.3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]17.7[/TD]
[TD="align: right"]17.5[/TD]
[TD="align: right"]17.2[/TD]
[TD="align: right"]16.9[/TD]
[TD="align: right"]16.6[/TD]
[TD="align: right"]16.3[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD="align: right"]225[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]18.8[/TD]
[TD="align: right"]18.5[/TD]
[TD="align: right"]18.2[/TD]
[TD="align: right"]17.9[/TD]
[TD="align: right"]17.6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]175[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]17.8[/TD]
[TD="align: right"]17.5[/TD]
[TD="align: right"]17.2[/TD]
[TD="align: right"]16.9[/TD]
[TD="align: right"]16.6[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD="align: right"]250[/TD]
[TD="align: right"]19.7[/TD]
[TD="align: right"]19.5[/TD]
[TD="align: right"]19.2[/TD]
[TD="align: right"]18.9[/TD]
[TD="align: right"]18.6[/TD]
[TD="align: right"]18.3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]18.7[/TD]
[TD="align: right"]18.5[/TD]
[TD="align: right"]18.2[/TD]
[TD="align: right"]17.9[/TD]
[TD="align: right"]17.6[/TD]
[TD="align: right"]17.3[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]11[/TD]
[TD][/TD]
[TD="align: right"]275[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]19.8[/TD]
[TD="align: right"]19.5[/TD]
[TD="align: right"]19.2[/TD]
[TD="align: right"]18.9[/TD]
[TD="align: right"]18.6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]225[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]18.8[/TD]
[TD="align: right"]18.5[/TD]
[TD="align: right"]18.2[/TD]
[TD="align: right"]17.9[/TD]
[TD="align: right"]17.6[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]12[/TD]
[TD][/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]20.7[/TD]
[TD="align: right"]20.5[/TD]
[TD="align: right"]20.2[/TD]
[TD="align: right"]19.9[/TD]
[TD="align: right"]19.6[/TD]
[TD="align: right"]19.3[/TD]
[TD]row 28[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]250[/TD]
[TD="align: right"]19.7[/TD]
[TD="align: right"]19.5[/TD]
[TD="align: right"]19.2[/TD]
[TD="align: right"]18.9[/TD]
[TD="align: right"]18.6[/TD]
[TD="align: right"]18.3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]275[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]19.8[/TD]
[TD="align: right"]19.5[/TD]
[TD="align: right"]19.2[/TD]
[TD="align: right"]18.9[/TD]
[TD="align: right"]18.6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]20.7[/TD]
[TD="align: right"]20.5[/TD]
[TD="align: right"]20.2[/TD]
[TD="align: right"]19.9[/TD]
[TD="align: right"]19.6[/TD]
[TD="align: right"]19.3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]first of all the correct table is pulled out and displayed[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]then the mpg figure for the specified parameters[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]MPG[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]18.5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]formula for L16 (lorry 2)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 12"]=IF(OFFSET($A$1,MATCH($L$13,$A$2:$A$40,0)+$K16,L$15)=0,"",OFFSET($A$1,MATCH($L$13,$A$2:$A$40,0)+$K16,L$15))[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]dragged across and down[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]formula for MPG (18.5)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 7"]=OFFSET($M$17,MATCH($N$13,$M$18:$M$28,0),MATCH($M$13,$N$17:$S$17,0))[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[dsmith1088]

Hi oldbrewer

Thank you very much for your reply. I think you might well have the answer for me but I'm finding it so hard to interpret because of the way the copy & pasting displays in the forum. Is there anyway you could should me an image/screenshot of your answer?

Thank you

 

[oldbrewer]

the original tables are in columns A to H - you could add another 100 lorries if you want - just change $a$40 to $a$2000

the offset works from A1 and searches column A for "lorry 2" [which is selected in cell L13]

the rest of the table is made by offsetting down and across

the formula for MPG is pretty self evident

what cannot you figure out - specifically

 

[Joe4]

Is there any way to post a screenshot - would be much clearer for you to see

There are tools you can use to post screen images. They are listed in Section B of this link here: Guidelines for Forum Use.
Also, there is a Test Here forum on this board that you can use to test out these tools to make sure they are working correctly before using them in your question.