What function will find a value in a table with X & Y Values

[Guest]

I have a table that has Temps along the x axis and Altitude down the Y axis.
Whats a formula that would display the value in the cell where a given Alt
value and Temp value meet?

Thanks

 

[Guest]

You could combine the VLOOKUP and MATCH functions. Something like this,
depending how your table is setup.

=VLOOKUP(A1,B1:F100,MATCH(A2,B1:F100,0),FALSE)

With Altitude and Temp to find stored in A1 and A2.

HTH,
Elkar

 

[Guest]

Thanks fo rthe respones but I'm not familar with those two functions, and am
can't fit what you provided into what I need.

So, with the table below, if 10-30 are column headers and 2000-7000 are row
headers, with a temp of 20 and a alt of 3500 given is there a formula that
will come up with the value 143 which is where the column and row intersect?

FYI the values in the table that are not headers will be random in the
actual table...


10 15 20 25 30
2000 111 112 113 114 115
2500 121 122 123 124 125
3000 131 132 133 134 135
3500 141 142 143 144 145
4000 151 152 153 154 155
4500 161 162 163 164 165
5000 171 172 173 174 175
5500 181 182 183 184 185
6000 191 192 193 194 195
6500 201 202 203 204 205
7000 211 212 213 214 215

 

[Biff]

Take a lookup at this screencap:

http://img98.imageshack.us/img98/6248/lookup4tc.jpg

Formula #1 is for EXACT matches of the lookup values. That is, your lookup
values will always match the table headers like 3500 and 20.

Formula #2 is for the CLOSEST match that is less than or equal to the lookup
value. That is, the lookup values may or may not match the table headers
like 3722 and 27.

Biff

 

[Guest]

Hi Brian!

As per example, supposing that we name the range 10 - 30 as "Temp", the
range 2000 - 7000 as "Alt" and the tabulated values as "data" (excluding Temp
and Alt) would this formula help?

=INDEX(data,MATCH(X,Alt,0),MATCH(Y,Temp,0))

where X and Y are desired Alt and Temp values respectively.

Hope this helps!

 

[Guest]

In addition to my post, be sure to have provisions within the spreadsheet to
place the X and Y values so that the indicated formula below can have valid
references.