Look up on X & Y Axis

[chester_Muchea]

I have a spreadsheet that I am trying to find multiple information.

Firstly I want to look up a number equal on the left hand column for floor length. Say I want a floor that is 10.3 long then I want it to grab the greater number, eg 10.5, then I want it to do the same across the other axis. eg. 3.5, therefore it would go to 3.6 and give me a cost of $5,200.

Secondary I need it then to go over to the 'Donuts required per slab' and pick the corresponding number, in this cast 4. I guess a Vlookup?

Thanks for you help

Floor Length	3.00	3.30	3.45	3.60	4.20	4.80	Donuts required per slab
16.0	$ -	$ -	$ -	$ 6,200.00	$ -	$ -	3.5	5.0
15.0	$5,300.00	$5,300.00	$5,350.00	$ 5,400.00	$ 5,500.00	$5,500.00	3.2	5.0
14.5	$5,300.00	$5,300.00	$5,350.00	$ 5,400.00	$ 5,500.00	$ -	3.1	5.0
14.4	$5,300.00	$5,300.00	$5,350.00	$ 5,400.00	$ 5,500.00	$ -	3.1	5.0
14.0	$5,250.00	$5,250.00	$5,300.00	$ 5,350.00	$ 5,450.00	$ -	3.0	4.0
13.5	$5,250.00	$5,250.00	$5,300.00	$ 5,350.00	$ 5,450.00	$ -	2.8	4.0
13.2	$5,200.00	$5,200.00	$5,250.00	$ 5,300.00	$ 5,400.00	$ -	2.8	4.0
13.0	$5,200.00	$5,200.00	$5,250.00	$ 5,300.00	$ 5,400.00	$ -	2.7	4.0
12.5	$5,200.00	$5,200.00	$5,250.00	$ 5,300.00	$ 5,400.00	$ -	2.6	4.0
12.0	$5,150.00	$5,150.00	$5,200.00	$ 5,250.00	$ 5,350.00	$ -	2.5	4.0
11.5	$5,150.00	$5,150.00	$5,200.00	$ 5,250.00	$ 5,350.00	$ -	2.3	4.0
11.0	$5,100.00	$5,100.00	$5,150.00	$ 5,200.00	$ 5,300.00	$ -	2.2	4.0
10.8	$5,100.00	$5,100.00	$5,150.00	$ 5,200.00	$ 5,300.00	$ -	2.2	4.0
10.5	$5,100.00	$5,100.00	$5,150.00	$ 5,200.00	$ 5,300.00	$ -	2.1	4.0
10.0	$5,050.00	$5,050.00	$5,100.00	$ 5,150.00	$ 5,250.00	$ -	2.0	3.0
9.6	$5,050.00	$5,050.00	$5,100.00	$ 5,150.00	$ 5,250.00	$ -	1.9	3.0
9.5	$5,050.00	$5,050.00	$5,100.00	$ 5,150.00	$ 5,250.00	$ -	1.8	3.0
9.0	$5,000.00	$5,000.00	$5,050.00	$ 5,100.00	$ 5,200.00	$ -	1.7	3.0
8.5	$5,000.00	$5,000.00	$5,050.00	$ 5,100.00	$ 5,200.00	$ -	1.6	3.0
8.4	$5,000.00	$5,000.00	$5,050.00	$ 5,100.00	$ 5,200.00	$ -	1.6	3.0
8.0	$5,000.00	$5,000.00	$5,050.00	$ 5,100.00	$ 5,200.00	$ -	1.5	3.0
7.5	$5,000.00	$5,000.00	$5,050.00	$ 5,100.00	$ 5,200.00	$ -	1.3	3.0
7.2	$5,000.00	$5,000.00	$5,050.00	$ 5,100.00	$ 5,200.00	$ -	1.3	3.0
7.0	$5,000.00	$5,000.00	$5,050.00	$ 5,100.00	$ 5,200.00	$ -	1.2	3.0
6.5	$5,000.00	$5,000.00	$5,050.00	$ 5,100.00	$ 5,200.00	$ -	1.1	3.0
6.0	$5,000.00	$5,000.00	$5,050.00	$ 5,100.00	$ 5,200.00	$ -	1.0	2.0

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

 

[Aladin Akyurek]

Let K2 house 10.3, a (row) look up value of interest.

Let L2 house 3.5, a (column/header) look up value of interest.

In M2 enter:

=VLOOKUP(INDEX($A$2:$A$27,MATCH($K2,$A$2:$A$27,-1)),$A$2:$G$27,1+MATCH(INDEX($B$1:$G$1,MATCH($L2,$B$1:$G$1,1)+(LOOKUP($L2,$B$1:$G$1) < $L2)),$B$1:$G$1,0),0)

And, by the way, care to describe how you obtain 4 from the range in H:I?

 

[chester_Muchea]

Let K2 house 10.3, a (row) look up value of interest.

Let L2 house 3.5, a (column/header) look up value of interest.

In M2 enter:

=VLOOKUP(INDEX($A$2:$A$27,MATCH($K2,$A$2:$A$27,-1)),$A$2:$G$27,1+MATCH(INDEX($B$1:$G$1,MATCH($L2,$B$1:$G$1,1)+(LOOKUP($L2,$B$1:$G$1) < $L2)),$B$1:$G$1,0),0)

And, by the way, care to describe how you obtain 4 from the range in H:I?

That works perfectly.

In relation to the 2nd part of the question. What ever the answer is from the first part, i need to then go to column 'I' and find the number. Column 'H' is not important for the answer.

Again, thank you for your help

 

[Aladin Akyurek]

That works perfectly.

Great.

In relation to the 2nd part of the question. What ever the answer is from the first part, i need to then go to column 'I' and find the number. Column 'H' is not important for the answer.

Not sure I'm getting this. Do you mean something like the following?

=VLOOKUP(INDEX($A$2:$A$27,MATCH($K2,$A$2:$A$27,-1)),$A$2:$I$27,9,0)

Again, thank you for your help

You are welcome.