Table interpolation

[ponedelj]

Hi. So im looking for formula to interpolate X and Y axis and to find interpolated value in table.

For example on top row 1.5 and side colum1.77 should give interpolate value in the table.

 

[ponedelj]

So here is table pic

 

[ponedelj]

Ok so table are something like
Y1/X1 -2 0 2 4
0,00 5815,8 5813 5809,5 5805,7
0,10 5811,8 5808,1 5804,2 5799,6
0,20 5805,7 5801,5 5797 5792,2
0,30 5797,7 5793,3 5788,5 5783,4
0,40 5787,5 5783,2 5778,4 5773,2
0,50 5774,9 5771,2 5766,7 5761,5
0,60 5760 5756,8 5752,8 5748,3
0,70 5743 5740,5 5737,3 5733,3
0,80 5724 5722,2 5719,8 5716,6
0,90 5703,1 5702,1 5700,5 5698,2
1,00 5680,5 5680,4 5679,6 5678,1

 

[dreid1011]

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))

 

[ponedelj]

N

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))

No. It should interpolate the X and Y value and find the interpolated Z value from the table.

 

[dreid1011]

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)

 

[ponedelj]

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)

Xlookup function is not working on this system.

 

[dreid1011]

Xlookup function is not working on this system.

Okay, let's try INDEX/MATCH/MATCH then. I saw you're on 2021 so I tried XLOOKUP first.

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))

 

[ponedelj]

It dont give intermediate value. For example for X 1.45

 

[dreid1011]

It dont give intermediate value. For example for X 1.45

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.

I was assuming you already had all the values calculated for each possible X,Y pair and you just wanted to look them up from your table... is this not accurate?