Book1 | |||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | |||
1 | 1.0 | 1.1 | 1.2 | 1.3 | 1.4 | 1.5 | 1.6 | 1.7 | 1.8 | 1.9 | X | Y | |||||
2 | 1.70 | 1.70 | 1.87 | 2.04 | 2.21 | 2.38 | 2.55 | 2.72 | 2.89 | 3.06 | 3.23 | 1.5 | 1.77 | 2.66 | |||
3 | 1.71 | 1.71 | 1.88 | 2.05 | 2.22 | 2.39 | 2.57 | 2.74 | 2.91 | 3.08 | 3.25 | ||||||
4 | 1.72 | 1.72 | 1.89 | 2.06 | 2.24 | 2.41 | 2.58 | 2.75 | 2.92 | 3.10 | 3.27 | ||||||
5 | 1.73 | 1.73 | 1.90 | 2.08 | 2.25 | 2.42 | 2.60 | 2.77 | 2.94 | 3.11 | 3.29 | ||||||
6 | 1.74 | 1.74 | 1.91 | 2.09 | 2.26 | 2.44 | 2.61 | 2.78 | 2.96 | 3.13 | 3.31 | ||||||
7 | 1.75 | 1.75 | 1.93 | 2.10 | 2.28 | 2.45 | 2.63 | 2.80 | 2.98 | 3.15 | 3.33 | ||||||
8 | 1.76 | 1.76 | 1.94 | 2.11 | 2.29 | 2.46 | 2.64 | 2.82 | 2.99 | 3.17 | 3.34 | ||||||
9 | 1.77 | 1.77 | 1.95 | 2.12 | 2.30 | 2.48 | 2.66 | 2.83 | 3.01 | 3.19 | 3.36 | ||||||
10 | 1.78 | 1.78 | 1.96 | 2.14 | 2.31 | 2.49 | 2.67 | 2.85 | 3.03 | 3.20 | 3.38 | ||||||
11 | 1.79 | 1.79 | 1.97 | 2.15 | 2.33 | 2.51 | 2.69 | 2.86 | 3.04 | 3.22 | 3.40 | ||||||
Sheet2 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
O2 | O2 | =XLOOKUP(M2,$B$1:$K$1,XLOOKUP(N2,$A$2:$A$11,$B$2:$K$11)) |
No. It should interpolate the X and Y value and find the interpolated Z value from the table.Maybe this:
Book1
A B C D E F G H I J K L M N O 1 1.0 1.1 1.2 1.3 1.4 1.5 1.6 1.7 1.8 1.9 X Y 2 1.70 1.70 1.87 2.04 2.21 2.38 2.55 2.72 2.89 3.06 3.23 1.5 1.77 2.66 3 1.71 1.71 1.88 2.05 2.22 2.39 2.57 2.74 2.91 3.08 3.25 4 1.72 1.72 1.89 2.06 2.24 2.41 2.58 2.75 2.92 3.10 3.27 5 1.73 1.73 1.90 2.08 2.25 2.42 2.60 2.77 2.94 3.11 3.29 6 1.74 1.74 1.91 2.09 2.26 2.44 2.61 2.78 2.96 3.13 3.31 7 1.75 1.75 1.93 2.10 2.28 2.45 2.63 2.80 2.98 3.15 3.33 8 1.76 1.76 1.94 2.11 2.29 2.46 2.64 2.82 2.99 3.17 3.34 9 1.77 1.77 1.95 2.12 2.30 2.48 2.66 2.83 3.01 3.19 3.36 10 1.78 1.78 1.96 2.14 2.31 2.49 2.67 2.85 3.03 3.20 3.38 11 1.79 1.79 1.97 2.15 2.33 2.51 2.69 2.86 3.04 3.22 3.40 Sheet2
Cell Formulas Range Formula O2 O2 =XLOOKUP(M2,$B$1:$K$1,XLOOKUP(N2,$A$2:$A$11,$B$2:$K$11))
Xlookup function is not working on this system.So, you're not wanting to look up the X and the Y and find the intersection? (Doesn't matter what values I used for the sample by the way)
Okay, let's try INDEX/MATCH/MATCH then. I saw you're on 2021 so I tried XLOOKUP first.Xlookup function is not working on this system.
Book1 | |||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | |||
1 | 1 | 1.1 | 1.2 | 1.3 | 1.4 | 1.5 | 1.6 | 1.7 | 1.8 | 1.9 | X | Y | |||||
2 | 1.7 | 1.7 | 1.87 | 2.04 | 2.21 | 2.38 | 2.55 | 2.72 | 2.89 | 3.06 | 3.23 | 1.5 | 1.77 | 2.66 | |||
3 | 1.71 | 1.71 | 1.881 | 2.052 | 2.223 | 2.394 | 2.565 | 2.736 | 2.907 | 3.078 | 3.249 | ||||||
4 | 1.72 | 1.72 | 1.892 | 2.064 | 2.236 | 2.408 | 2.58 | 2.752 | 2.924 | 3.096 | 3.268 | ||||||
5 | 1.73 | 1.73 | 1.903 | 2.076 | 2.249 | 2.422 | 2.595 | 2.768 | 2.941 | 3.114 | 3.287 | ||||||
6 | 1.74 | 1.74 | 1.914 | 2.088 | 2.262 | 2.436 | 2.61 | 2.784 | 2.958 | 3.132 | 3.306 | ||||||
7 | 1.75 | 1.75 | 1.925 | 2.1 | 2.275 | 2.45 | 2.625 | 2.8 | 2.975 | 3.15 | 3.325 | ||||||
8 | 1.76 | 1.76 | 1.936 | 2.112 | 2.288 | 2.464 | 2.64 | 2.816 | 2.992 | 3.168 | 3.344 | ||||||
9 | 1.77 | 1.77 | 1.947 | 2.124 | 2.301 | 2.478 | 2.655 | 2.832 | 3.009 | 3.186 | 3.363 | ||||||
10 | 1.78 | 1.78 | 1.958 | 2.136 | 2.314 | 2.492 | 2.67 | 2.848 | 3.026 | 3.204 | 3.382 | ||||||
11 | 1.79 | 1.79 | 1.969 | 2.148 | 2.327 | 2.506 | 2.685 | 2.864 | 3.043 | 3.222 | 3.401 | ||||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
O2 | O2 | =INDEX($B$2:$K$11,MATCH(N2,$A$2:$A$11,0),MATCH(M2,$B$1:$K$1,0)) |
If you're using the sample values I provided, then it won't because 1.45 is not in the top row for X values. If you want it to return a result for a specific X,Y combo, then it has to be present in the table already. If you're wanting it to calculate something else based on the X,Y inputs, then that will be an entirely different formula.It dont give intermediate value. For example for X 1.45