Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
What function will find a value in a table with X & Y Values
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
What function will find a value in a table with X & Y Values
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 "Brian" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
What function will find a value in a table with X & Y Values
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 "Elkar" wrote: 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 "Brian" wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
What function will find a value in a table with X & Y Values
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 "Brian" wrote in message ... 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 "Elkar" wrote: 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 "Brian" wrote: 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
What function will find a value in a table with X & Y Values
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! -- Thanks and kind regards "Brian" wrote: 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 "Elkar" wrote: 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 "Brian" wrote: 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
What function will find a value in a table with X & Y Values
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. -- Thanks and kind regards "RaymundCG" wrote: 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! -- Thanks and kind regards "Brian" wrote: 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 "Elkar" wrote: 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 "Brian" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Min Function Excluding Zero Values & More | Excel Worksheet Functions | |||
Match function in a two input table | Excel Worksheet Functions | |||
Error Handling #N/A with AVERAGE Function - Average of values in Row | Excel Worksheet Functions | |||
Why won't pasted values from a formula appear in a pivot table | Excel Discussion (Misc queries) | |||
Lookup Table Dilemma | Excel Worksheet Functions |