Return Values Offset from Rightmost Cells

[paulfitz320]

Hi,
I have a formula in COL P that returns the value in the Rightmost cell in the row between COL A:M
Need formulas in COL N that returns the value offset 2 to the left, and COL O that returns the value offset 1 to the left, of the rightmost value.
Thanks

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	col1	col2	col3	col4	col5	col6	col7	col8	col9	col10	col11			wanted1	wanted2	col11
2	12.09	11.72	11.26	11.77	12.99	1m 13.95s	24.76	100.74 %						1m 13.95s	24.76	100.74 %
3	12.01	11.80	11.29	11.70	13.20	1m 14.41s	24.91	100.76 %						1m 14.41s	24.91	100.76 %
4	12.23	11.85	11.13	11.69	13.43	1m 14.50s	25.12	100.04 %						1m 14.50s	25.12	100.04 %
5	12.11	11.71	11.44	11.93	13.39	1m 14.54s	25.32	99.31 %						1m 14.54s	25.32	99.31 %
6	12.06	11.70	11.23	11.80	13.68	1m 14.62s	25.48	98.75 %						1m 14.62s	25.48	98.75 %
7	12.29	11.76	11.42	11.92	13.84	1m 14.93s	25.76	98.12 %						1m 14.93s	25.76	98.12 %
8	11.81	11.65	11.36	12.07	13.18	1m 15.15s	25.25	100.37 %						1m 15.15s	25.25	100.37 %
9	12.21	11.64	11.43	12.22	13.93	1m 15.38s	26.15	97.23 %						1m 15.38s	26.15	97.23 %
10	12.35	11.87	11.48	12.21	14.08	1m 16.08s	26.29	97.60 %						1m 16.08s	26.29	97.60 %
11	13.07	13.79	13.14	12.42	11.79	12.07	12.78	13.02	2m 09.71s	37.87	102.06 %			2m 09.71s	37.87	102.06 %
12	12.90	14.18	12.92	12.39	11.70	12.01	12.79	12.94	2m 09.73s	37.73	102.44 %			2m 09.73s	37.73	102.44 %
13	13.02	13.55	13.37	12.23	11.72	12.13	12.87	13.40	2m 09.89s	38.41	100.76 %			2m 09.89s	38.41	100.76 %
14	13.38	14.23	13.01	12.31	11.76	11.82	12.73	13.00	2m 10.31s	37.55	103.41 %			2m 10.31s	37.55	103.41 %
15	13.14	13.88	12.99	12.30	11.74	12.03	12.70	13.22	2m 10.40s	37.95	102.38 %			2m 10.40s	37.95	102.38 %
16	13.38	14.01	12.82	12.25	11.80	11.79	12.62	13.23	2m 10.55s	37.64	103.36 %			2m 10.55s	37.64	103.36 %
17	12.99	13.72	13.26	12.37	11.92	11.95	13.38	13.34	2m 10.58s	38.66	100.63 %			2m 10.58s	38.66	100.63 %
18	13.26	14.10	13.15	12.16	11.57	11.84	12.80	13.30	2m 10.74s	37.94	102.69 %			2m 10.74s	37.94	102.69 %
19	13.13	13.97	13.04	12.17	11.85	12.27	12.88	13.65	2m 10.87s	38.80	100.51 %			2m 10.87s	38.80	100.51 %
20	12.91	13.69	13.46	12.23	11.83	12.15	13.35	14.16	2m 11.18s	39.66	98.55 %			2m 11.18s	39.66	98.55 %
21	13.25	14.24	13.07	12.25	11.53	12.12	12.75	13.80	2m 11.26s	38.67	101.15 %			2m 11.26s	38.67	101.15 %
22	13.01	14.23	12.82	12.42	11.85	12.33	14.05	16.45	2m 15.03s	42.84	93.92 %			2m 15.03s	42.84	93.92 %
23	13.18	13.98	13.07	12.36	11.97	12.80	14.96	14.53	2m 15.28s	42.30	95.30 %			2m 15.28s	42.30	95.30 %
Sheet1

Cell Formulas
Range	Formula
P1:P23	P1	=LOOKUP(2,1/(A1:M1<>""),A1:M1)

 

[RoryA]

Are there ever any blank cells within the actual data (i.e. between valid values)?

If not, then clear columns N:P and enter this in N2 and copy down:

=CHOOSECOLS(A2:M2,SEQUENCE(,3,COUNTA(A2:M2)-2))

 

[awoohaw]

another possibility:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	col1	col2	col3	col4	col5	col6	col7	col8	col9	col10	col11			wanted1	wanted2	col11
2	12.09	11.72	11.26	11.77	12.99	1m 13.95s	24.76	100.74%						1m 13.95s	24.76	1.0074
3	12.01	11.8	11.29	11.7	13.2	1m 14.41s	24.91	100.76%						1m 14.41s	24.91	1.0076
4	12.23	11.85	11.13	11.69	13.43	1m 14.50s	25.12	100.04%						1m 14.50s	25.12	1.0004
5	12.11	11.71	11.44	11.93	13.39	1m 14.54s	25.32	99.31%						1m 14.54s	25.32	0.9931
6	12.06	11.7	11.23	11.8	13.68	1m 14.62s	25.48	98.75%						1m 14.62s	25.48	0.9875
7	12.29	11.76	11.42	11.92	13.84	1m 14.93s	25.76	98.12%						1m 14.93s	25.76	0.9812
8	11.81	11.65	11.36	12.07	13.18	1m 15.15s	25.25	100.37%						1m 15.15s	25.25	1.0037
9	12.21	11.64	11.43	12.22	13.93	1m 15.38s	26.15	97.23%						1m 15.38s	26.15	0.9723
10	12.35	11.87	11.48	12.21	14.08	1m 16.08s	26.29	97.60%						1m 16.08s	26.29	0.976
11	13.07	13.79	13.14	12.42	11.79	12.07	12.78	13.02	2m 09.71s	37.87	102.06%			2m 09.71s	37.87	1.0206
12	12.9	14.18	12.92	12.39	11.7	12.01	12.79	12.94	2m 09.73s	37.73	102.44%			2m 09.73s	37.73	1.0244
13	13.02	13.55	13.37	12.23	11.72	12.13	12.87	13.4	2m 09.89s	38.41	100.76%			2m 09.89s	38.41	1.0076
14	13.38	14.23	13.01	12.31	11.76	11.82	12.73	13	2m 10.31s	37.55	103.41%			2m 10.31s	37.55	1.0341
15	13.14	13.88	12.99	12.3	11.74	12.03	12.7	13.22	2m 10.40s	37.95	102.38%			2m 10.40s	37.95	1.0238
16	13.38	14.01	12.82	12.25	11.8	11.79	12.62	13.23	2m 10.55s	37.64	103.36%			2m 10.55s	37.64	1.0336
17	12.99	13.72	13.26	12.37	11.92	11.95	13.38	13.34	2m 10.58s	38.66	100.63%			2m 10.58s	38.66	1.0063
18	13.26	14.1	13.15	12.16	11.57	11.84	12.8	13.3	2m 10.74s	37.94	102.69%			2m 10.74s	37.94	1.0269
19	13.13	13.97	13.04	12.17	11.85	12.27	12.88	13.65	2m 10.87s	38.8	100.51%			2m 10.87s	38.8	1.0051
20	12.91	13.69	13.46	12.23	11.83	12.15	13.35	14.16	2m 11.18s	39.66	98.55%			2m 11.18s	39.66	0.9855
21	13.25	14.24	13.07	12.25	11.53	12.12	12.75	13.8	2m 11.26s	38.67	101.15%			2m 11.26s	38.67	1.0115
22	13.01	14.23	12.82	12.42	11.85	12.33	14.05	16.45	2m 15.03s	42.84	93.92%			2m 15.03s	42.84	0.9392
23	13.18	13.98	13.07	12.36	11.97	12.8	14.96	14.53	2m 15.28s	42.3	95.30%			2m 15.28s	42.3	0.953
Sheet1

Cell Formulas
Range	Formula
P1:P23	P1	=LOOKUP(2,1/(A1:M1<>""),A1:M1)
N2:N23	N2	=INDEX($A2:$K2,MAX(($A2:$K2<>"")*(COLUMN($A2:$K2)))-2)
O2:O23	O2	=INDEX($A2:$K2,MAX(($A2:$K2<>"")*(COLUMN($A2:$K2)))-1)

 

[paulfitz320]

Each solution worked fine. Thanks very much to you both.