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

Vehicle reg	XYZ 5000	User enterred
Mileage	150	User enterred
Tonnage	2.4	User enterred
MPG		To be calculatedCalculated
			XYZ 5000		Mileage
					100	200
			Tonnage	1	10	5
			2	9	4
			3	8	3
			XYZ 1000		Mileage
					100	300
			Tonnage	1	11	5
			2	7	5
			3	6	4

<colgroup><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

David

 

[oldbrewer]

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

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col span="10"></colgroup><tbody>
</tbody>

 

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