how do i select data based on two inputs in excel

B

Bluebell

Pressure
Psig Inlet Temperature
90 95 100 105 110 115 120

60 1.156 1.289 1.451 1.643 1.915 2.296 2.793
70 1.022 1.140 1.283 1.453 1.694 2.030 2.470
80 0.929 1.039 1.166 1.320 1.539 1.844 2.244
90 0.855 0.954 1.074 1.216 1.417 1.699 2.067
100 0.797 0.888 1.000 1.132 1.320 1.582 1.925
110 0.742 0.828 0.932 1.055 1.230 1.474 1.793
120 0.700 0.775 0.872 0.987 1.151 1.379 1.678
130 0.700 0.737 0.829 0.939 1.095 1.312 1.596
140 0.700 0.707 0.796 1.901 1.047 1.255 1.527
150 0.700 0.700 0.768 0.870 1.012 1.213 1.476
160 0.700 0.700 0.742 0.841 0.979 1.174 1.428
170 0.700 0.700 0.717 0.812 0.947 1.135 1.380
180 0.700 0.700 0.700 0.784 0.913 1.095 1.332
190 0.700 0.700 0.700 0.755 0.880 1.055 1.283
200 0.700 0.700 0.700 0.728 0.849 1.018 1.238
210 0.700 0.700 0.700 0.704 0.820 0.983 1.196
220 0.700 0.700 0.700 0.700 0.796 0.955 1.161
230 0.700 0.700 0.700 0.700 0.772 0.925 1.126
240 0.700 0.700 0.700 0.700 0.750 0.899 1.094
250 0.700 0.700 0.700 0.700 0.728 0.873 1.062
260 0.700 0.700 0.700 0.700 0.709 0.849 1.033
270 0.700 0.700 0.700 0.700 0.700 0.825 1.004
280 0.700 0.700 0.700 0.700 0.700 0.802 0.976
Click to expand...

I need to be able to retrieve the corresponding data based on two input
values, the results and input values are in one worksheet (1) and the data
(as above) in another(2). For example : i need to find the corresponding
value for a pressure of 70psig at a temperature of 100oF. The result should
be 1.283.
This result should be based on inputs in two cells(C8(pressure) &
C9(temperature) in worksheet 1)
 
R

Ron Coderre

Try this:

With
Your data table on Sheet2, Cells A3:H26
.....B3:=90, C3:95, etc
.....A4:=60, A5:=70, etc

On Sheet1
C8: 70
C9: 100
C10: =VLOOKUP(C8,Sheet2!$A$3:$H$26,MATCH(C9,Sheet2!$A$3:$H$3,0),1)

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP
 
F

Franz Verga

Bluebell said:
Pressure
Psig Inlet Temperature
90 95 100 105 110 115 120

60 1.156 1.289 1.451 1.643 1.915 2.296 2.793
70 1.022 1.140 1.283 1.453 1.694 2.030 2.470
80 0.929 1.039 1.166 1.320 1.539 1.844 2.244
90 0.855 0.954 1.074 1.216 1.417 1.699 2.067
100 0.797 0.888 1.000 1.132 1.320 1.582 1.925
110 0.742 0.828 0.932 1.055 1.230 1.474 1.793
120 0.700 0.775 0.872 0.987 1.151 1.379 1.678
130 0.700 0.737 0.829 0.939 1.095 1.312 1.596
140 0.700 0.707 0.796 1.901 1.047 1.255 1.527
150 0.700 0.700 0.768 0.870 1.012 1.213 1.476
160 0.700 0.700 0.742 0.841 0.979 1.174 1.428
170 0.700 0.700 0.717 0.812 0.947 1.135 1.380
180 0.700 0.700 0.700 0.784 0.913 1.095 1.332
190 0.700 0.700 0.700 0.755 0.880 1.055 1.283
200 0.700 0.700 0.700 0.728 0.849 1.018 1.238
210 0.700 0.700 0.700 0.704 0.820 0.983 1.196
220 0.700 0.700 0.700 0.700 0.796 0.955 1.161
230 0.700 0.700 0.700 0.700 0.772 0.925 1.126
240 0.700 0.700 0.700 0.700 0.750 0.899 1.094
250 0.700 0.700 0.700 0.700 0.728 0.873 1.062
260 0.700 0.700 0.700 0.700 0.709 0.849 1.033
270 0.700 0.700 0.700 0.700 0.700 0.825 1.004

I need to be able to retrieve the corresponding data based on two
input values, the results and input values are in one worksheet (1)
and the data (as above) in another(2). For example : i need to find
the corresponding value for a pressure of 70psig at a temperature of
100oF. The result should be 1.283.
This result should be based on inputs in two cells(C8(pressure) &
C9(temperature) in worksheet 1)
Click to expand...


I made a small example based on OFFSET and MATCH function. You can download
from:

http://rapidshare.de/files/31086614/pressure_-_temperature.xls.html

The formula I used is:

=OFFSET(Sheet2!A2,MATCH(Sheet1!C8,Sheet2!$A$2:$A$26,0)-1,MATCH(Sheet1!C9,Sheet2!$A$2:$H$2,0)-1)

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy
 
R

Ron Coderre

A couple comments about my post:

I eliminated the blank row under the Inlet Temperatures
I changed the >280 at the bottom of the left column to simply 280

***********
Regards,
Ron

XL2002, WinXP
 
Top