Selected a cell depending on 2 factors

[Josh88]

Hi,

I have 2 elements that I would like to use to find the final result. I have quantity and length, upon which I am looking for price per unit. Here is a table of the information with quantity along the X axis and length down the Y:
[TABLE="class: cms_table_grid, width: 350, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]2500[/TD]
[TD]5000[/TD]
[TD]10,000[/TD]
[TD]15,000[/TD]
[/TR]
[TR]
[TD]78m[/TD]
[TD]0.76[/TD]
[TD]0.73[/TD]
[TD]0.71[/TD]
[TD]0.70[/TD]
[/TR]
[TR]
[TD]90m[/TD]
[TD]0.84[/TD]
[TD]0.81[/TD]
[TD]0.79[/TD]
[TD]0.78[/TD]
[/TR]
[TR]
[TD]92m[/TD]
[TD]0.85[/TD]
[TD]0.82[/TD]
[TD]0.81[/TD]
[TD]0.80[/TD]
[/TR]
</tbody>[/TABLE]


I would like to have an automated calculation whereby I can build a basic info box containing the number of units required and their length, and the matching unit price is inserted.

[TABLE="class: cms_table_grid, width: 300, align: left"]
<tbody>[TR]
[TD]Number of Units[/TD]
[TD]5000[/TD]
[/TR]
[TR]
[TD]Length[/TD]
[TD]90m[/TD]
[/TR]
[TR]
[TD]Price per unit[/TD]
[TD] -???-[/TD]
[/TR]
</tbody>[/TABLE]



Thank you very much Excel wizards!

 

[jarjarbingie]

Hi,

I have 2 elements that I would like to use to find the final result. I have quantity and length, upon which I am looking for price per unit. Here is a table of the information with quantity along the X axis and length down the Y:
[TABLE="class: cms_table_grid, width: 350, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]2500[/TD]
[TD]5000[/TD]
[TD]10,000[/TD]
[TD]15,000[/TD]
[/TR]
[TR]
[TD]78m[/TD]
[TD]0.76[/TD]
[TD]0.73[/TD]
[TD]0.71[/TD]
[TD]0.70[/TD]
[/TR]
[TR]
[TD]90m[/TD]
[TD]0.84[/TD]
[TD]0.81[/TD]
[TD]0.79[/TD]
[TD]0.78[/TD]
[/TR]
[TR]
[TD]92m[/TD]
[TD]0.85[/TD]
[TD]0.82[/TD]
[TD]0.81[/TD]
[TD]0.80[/TD]
[/TR]
</tbody>[/TABLE]


I would like to have an automated calculation whereby I can build a basic info box containing the number of units required and their length, and the matching unit price is inserted.

[TABLE="class: cms_table_grid, width: 300, align: left"]
<tbody>[TR]
[TD]Number of Units[/TD]
[TD]5000[/TD]
[/TR]
[TR]
[TD]Length[/TD]
[TD]90m[/TD]
[/TR]
[TR]
[TD]Price per unit[/TD]
[TD] -???-[/TD]
[/TR]
</tbody>[/TABLE]



Thank you very much Excel wizards!

=INDEX(Sheet1!$B$2:$E$4,MATCH(B2,Sheet1!$A$2:$A$4,0),MATCH(B1,Sheet1!$B$1:$E$1,0))

where the first table is in Sheet1

 

[Josh88]

Thanks,
So if both tables are in the same sheet, I can simply remove the Sheet1! ​?

 

[jarjarbingie]

Thanks,
So if both tables are in the same sheet, I can simply remove the Sheet1! ​?

yes, just change the references to the correct cells..