offset and match? question

[ebsmcb]

I'm trying to return the cell contents -1 column away from the first occurrence of a repeating text string... See below for an example. I've tried using various combinations of offset and match to no avail.

sample #		data 1	data 2	data 3	data 4	data 5	etc
1	return contents of orange text here	3	4	5	na	na
2	return contents of orange text here	10	8	na	na	na
3	return contents of orange text here	16	na	na	na	na
etc	etc

 

[Fluff]

Hi & welcome to MrExcel.
Will the repeating string always be na?

 

[DanteAmor]

Hi and welcome to Mr

Try:

varios 21jun2023.xlsm
	A	B	C	D	E	F	G	H
1	sample #		data 1	data 2	data 3	data 4	data 5	etc
2	1	5	3	4	5	na	na
3	2	8	10	8	na	na	na
4	3	16	16	na	na	na	na
5	etc
Hoja2

Cell Formulas
Range	Formula
B2:B5	B2	=IFERROR(INDEX(C2:Z2,, MATCH("na",C2:Z2,0)-1),"")

 

[ebsmcb]

Hi & welcome to MrExcel.
Will the repeating string always be na?

yes... always na.

 

[Fluff]

I that case try Dante's suggestion.

 

[ebsmcb]

Hi and welcome to Mr

Try:

varios 21jun2023.xlsm
	A	B	C	D	E	F	G	H
1	sample #		data 1	data 2	data 3	data 4	data 5	etc
2	1	5	3	4	5	na	na
3	2	8	10	8	na	na	na
4	3	16	16	na	na	na	na
5	etc
Hoja2

Cell Formulas
Range	Formula
B2:B5	B2	=IFERROR(INDEX(C2:Z2,, MATCH("na",C2:Z2,0)-1),"")

Hi and welcome to Mr

Try:

varios 21jun2023.xlsm
	A	B	C	D	E	F	G	H
1	sample #		data 1	data 2	data 3	data 4	data 5	etc
2	1	5	3	4	5	na	na
3	2	8	10	8	na	na	na
4	3	16	16	na	na	na	na
5	etc
Hoja2

Cell Formulas
Range	Formula
B2:B5	B2	=IFERROR(INDEX(C2:Z2,, MATCH("na",C2:Z2,0)-1),"")

The formula above =IFERROR(INDEX(C2:Z2,, MATCH("na",C2:Z2,0)-1),"") worked for the first row (returned "5", but returned "na" for 2nd and 3rd rows... Not sure why!

 

[Fluff]

How about

=IFNA(INDEX(C2:X2,XMATCH("na",C2:X2)-1),"none")

 

[DanteAmor]

Are you testing with the data you put in your first post?
Or is it other data?
If they are other data, you could put them here and also the result that you want.

Note XL2BB:​

For the future, it would help greatly if you could give us the sample data in a form that we can copy to test with.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in
Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[ebsmcb]

OK... I'll try the copy/paste with XL2BB... See below. Still returning "na" in rows 2 and 3 for some reason. This is the actual data (pasted below). Thanks y'all!

Sample #	Date/Time		Frequency (MHz)	Profile Type	Depth (m)	Cell Size (m)	Cell Start (m)	Cell1 Location (m)	Cell1 Ve (m/s)	Cell1 Vn (m/s)	Cell1 Vu (m/s)	Cell1 Vd (m/s)	Cell1 Spd (m/s)	Cell1 Dir (deg)	Cell2 Location (m)	Cell2 Ve (m/s)	Cell2 Vn (m/s)	Cell2 Vu (m/s)	Cell2 Vd (m/s)	Cell2 Spd (m/s)	Cell2 Dir (deg)	Cell3 Location (m)	Cell3 Ve (m/s)	Cell3 Vn (m/s)	Cell3 Vu (m/s)	Cell3 Vd (m/s)	Cell3 Spd (m/s)	Cell3 Dir (deg)	Cell4 Location (m)	Cell4 Ve (m/s)	Cell4 Vn (m/s)	Cell4 Vu (m/s)	Cell4 Vd (m/s)	Cell4 Spd (m/s)	Cell4 Dir (deg)	Cell5 Location (m)	Cell5 Ve (m/s)	Cell5 Vn (m/s)	Cell5 Vu (m/s)	Cell5 Vd (m/s)	Cell5 Spd (m/s)	Cell5 Dir (deg)	Cell6 Location (m)	Cell6 Ve (m/s)	Cell6 Vn (m/s)	Cell6 Vu (m/s)	Cell6 Vd (m/s)	Cell6 Spd (m/s)	Cell6 Dir (deg)	Cell7 Location (m)	Cell7 Ve (m/s)	Cell7 Vn (m/s)	Cell7 Vu (m/s)	Cell7 Vd (m/s)	Cell7 Spd (m/s)	Cell7 Dir (deg)	Cell8 Location (m)	Cell8 Ve (m/s)	Cell8 Vn (m/s)	Cell8 Vu (m/s)	Cell8 Vd (m/s)	Cell8 Spd (m/s)	Cell8 Dir (deg)	Cell9 Location (m)	Cell9 Ve (m/s)	Cell9 Vn (m/s)	Cell9 Vu (m/s)	Cell9 Vd (m/s)	Cell9 Spd (m/s)	Cell9 Dir (deg)	Cell10 Location (m)	Cell10 Ve (m/s)	Cell10 Vn (m/s)	Cell10 Vu (m/s)	Cell10 Vd (m/s)	Cell10 Spd (m/s)	Cell10 Dir (deg)	Cell11 Location (m)	Cell11 Ve (m/s)	Cell11 Vn (m/s)	Cell11 Vu (m/s)	Cell11 Vd (m/s)	Cell11 Spd (m/s)	Cell11 Dir (deg)	Cell12 Location (m)	Cell12 Ve (m/s)	Cell12 Vn (m/s)	Cell12 Vu (m/s)	Cell12 Vd (m/s)	Cell12 Spd (m/s)	Cell12 Dir (deg)	Cell13 Location (m)	Cell13 Ve (m/s)	Cell13 Vn (m/s)	Cell13 Vu (m/s)	Cell13 Vd (m/s)	Cell13 Spd (m/s)	Cell13 Dir (deg)	Cell14 Location (m)	Cell14 Ve (m/s)	Cell14 Vn (m/s)	Cell14 Vu (m/s)	Cell14 Vd (m/s)	Cell14 Spd (m/s)	Cell14 Dir (deg)	Cell15 Location (m)	Cell15 Ve (m/s)	Cell15 Vn (m/s)	Cell15 Vu (m/s)	Cell15 Vd (m/s)	Cell15 Spd (m/s)	Cell15 Dir (deg)	Cell16 Location (m)	Cell16 Ve (m/s)	Cell16 Vn (m/s)	Cell16 Vu (m/s)	Cell16 Vd (m/s)	Cell16 Spd (m/s)	Cell16 Dir (deg)	Cell17 Location (m)	Cell17 Ve (m/s)	Cell17 Vn (m/s)	Cell17 Vu (m/s)	Cell17 Vd (m/s)	Cell17 Spd (m/s)	Cell17 Dir (deg)	Cell18 Location (m)	Cell18 Ve (m/s)	Cell18 Vn (m/s)	Cell18 Vu (m/s)	Cell18 Vd (m/s)	Cell18 Spd (m/s)	Cell18 Dir (deg)	Cell19 Location (m)	Cell19 Ve (m/s)	Cell19 Vn (m/s)	Cell19 Vu (m/s)	Cell19 Vd (m/s)	Cell19 Spd (m/s)	Cell19 Dir (deg)	Cell20 Location (m)	Cell20 Ve (m/s)	Cell20 Vn (m/s)	Cell20 Vu (m/s)	Cell20 Vd (m/s)	Cell20 Spd (m/s)	Cell20 Dir (deg)	Cell21 Location (m)	Cell21 Ve (m/s)	Cell21 Vn (m/s)	Cell21 Vu (m/s)	Cell21 Vd (m/s)	Cell21 Spd (m/s)	Cell21 Dir (deg)	Cell22 Location (m)	Cell22 Ve (m/s)	Cell22 Vn (m/s)	Cell22 Vu (m/s)	Cell22 Vd (m/s)	Cell22 Spd (m/s)	Cell22 Dir (deg)	Cell23 Location (m)	Cell23 Ve (m/s)	Cell23 Vn (m/s)	Cell23 Vu (m/s)	Cell23 Vd (m/s)	Cell23 Spd (m/s)	Cell23 Dir (deg)	Cell24 Location (m)	Cell24 Ve (m/s)	Cell24 Vn (m/s)	Cell24 Vu (m/s)	Cell24 Vd (m/s)	Cell24 Spd (m/s)	Cell24 Dir (deg)	Cell25 Location (m)	Cell25 Ve (m/s)	Cell25 Vn (m/s)	Cell25 Vu (m/s)	Cell25 Vd (m/s)	Cell25 Spd (m/s)	Cell25 Dir (deg)	Cell26 Location (m)	Cell26 Ve (m/s)	Cell26 Vn (m/s)	Cell26 Vu (m/s)	Cell26 Vd (m/s)	Cell26 Spd (m/s)	Cell26 Dir (deg)	Cell27 Location (m)	Cell27 Ve (m/s)	Cell27 Vn (m/s)	Cell27 Vu (m/s)	Cell27 Vd (m/s)	Cell27 Spd (m/s)	Cell27 Dir (deg)	Cell28 Location (m)	Cell28 Ve (m/s)	Cell28 Vn (m/s)	Cell28 Vu (m/s)	Cell28 Vd (m/s)	Cell28 Spd (m/s)	Cell28 Dir (deg)	Cell29 Location (m)	Cell29 Ve (m/s)	Cell29 Vn (m/s)	Cell29 Vu (m/s)	Cell29 Vd (m/s)	Cell29 Spd (m/s)	Cell29 Dir (deg)	Cell30 Location (m)	Cell30 Ve (m/s)	Cell30 Vn (m/s)	Cell30 Vu (m/s)	Cell30 Vd (m/s)	Cell30 Spd (m/s)	Cell30 Dir (deg)	Cell31 Location (m)	Cell31 Ve (m/s)	Cell31 Vn (m/s)	Cell31 Vu (m/s)	Cell31 Vd (m/s)	Cell31 Spd (m/s)	Cell31 Dir (deg)	Cell32 Location (m)	Cell32 Ve (m/s)	Cell32 Vn (m/s)	Cell32 Vu (m/s)	Cell32 Vd (m/s)	Cell32 Spd (m/s)	Cell32 Dir (deg)	Cell33 Location (m)	Cell33 Ve (m/s)	Cell33 Vn (m/s)	Cell33 Vu (m/s)	Cell33 Vd (m/s)	Cell33 Spd (m/s)	Cell33 Dir (deg)	Cell34 Location (m)	Cell34 Ve (m/s)	Cell34 Vn (m/s)	Cell34 Vu (m/s)	Cell34 Vd (m/s)	Cell34 Spd (m/s)	Cell34 Dir (deg)	Cell35 Location (m)	Cell35 Ve (m/s)	Cell35 Vn (m/s)	Cell35 Vu (m/s)	Cell35 Vd (m/s)	Cell35 Spd (m/s)	Cell35 Dir (deg)	Cell36 Location (m)	Cell36 Ve (m/s)	Cell36 Vn (m/s)	Cell36 Vu (m/s)	Cell36 Vd (m/s)	Cell36 Spd (m/s)	Cell36 Dir (deg)	Cell37 Location (m)	Cell37 Ve (m/s)	Cell37 Vn (m/s)	Cell37 Vu (m/s)	Cell37 Vd (m/s)	Cell37 Spd (m/s)	Cell37 Dir (deg)	Cell38 Location (m)	Cell38 Ve (m/s)	Cell38 Vn (m/s)	Cell38 Vu (m/s)	Cell38 Vd (m/s)	Cell38 Spd (m/s)	Cell38 Dir (deg)	Cell39 Location (m)	Cell39 Ve (m/s)	Cell39 Vn (m/s)	Cell39 Vu (m/s)	Cell39 Vd (m/s)	Cell39 Spd (m/s)	Cell39 Dir (deg)	Cell40 Location (m)	Cell40 Ve (m/s)	Cell40 Vn (m/s)	Cell40 Vu (m/s)	Cell40 Vd (m/s)	Cell40 Spd (m/s)	Cell40 Dir (deg)	Cell41 Location (m)	Cell41 Ve (m/s)	Cell41 Vn (m/s)	Cell41 Vu (m/s)	Cell41 Vd (m/s)	Cell41 Spd (m/s)	Cell41 Dir (deg)	Cell42 Location (m)	Cell42 Ve (m/s)	Cell42 Vn (m/s)	Cell42 Vu (m/s)	Cell42 Vd (m/s)	Cell42 Spd (m/s)	Cell42 Dir (deg)	Cell43 Location (m)	Cell43 Ve (m/s)	Cell43 Vn (m/s)	Cell43 Vu (m/s)	Cell43 Vd (m/s)	Cell43 Spd (m/s)	Cell43 Dir (deg)	Cell44 Location (m)	Cell44 Ve (m/s)	Cell44 Vn (m/s)	Cell44 Vu (m/s)	Cell44 Vd (m/s)	Cell44 Spd (m/s)	Cell44 Dir (deg)	Cell45 Location (m)	Cell45 Ve (m/s)	Cell45 Vn (m/s)	Cell45 Vu (m/s)	Cell45 Vd (m/s)	Cell45 Spd (m/s)	Cell45 Dir (deg)	Cell46 Location (m)	Cell46 Ve (m/s)	Cell46 Vn (m/s)	Cell46 Vu (m/s)	Cell46 Vd (m/s)	Cell46 Spd (m/s)	Cell46 Dir (deg)	Cell47 Location (m)	Cell47 Ve (m/s)	Cell47 Vn (m/s)	Cell47 Vu (m/s)	Cell47 Vd (m/s)	Cell47 Spd (m/s)	Cell47 Dir (deg)	Cell48 Location (m)	Cell48 Ve (m/s)	Cell48 Vn (m/s)	Cell48 Vu (m/s)	Cell48 Vd (m/s)	Cell48 Spd (m/s)	Cell48 Dir (deg)	Cell49 Location (m)	Cell49 Ve (m/s)	Cell49 Vn (m/s)	Cell49 Vu (m/s)	Cell49 Vd (m/s)	Cell49 Spd (m/s)	Cell49 Dir (deg)	Cell50 Location (m)	Cell50 Ve (m/s)	Cell50 Vn (m/s)	Cell50 Vu (m/s)	Cell50 Vd (m/s)	Cell50 Spd (m/s)	Cell50 Dir (deg)	Cell51 Location (m)	Cell51 Ve (m/s)	Cell51 Vn (m/s)	Cell51 Vu (m/s)	Cell51 Vd (m/s)	Cell51 Spd (m/s)	Cell51 Dir (deg)
1	5/16/23 14:43	yellow highlight here	3MHz	IC	1.26	0.1	0.15	0.2	-0.436	0.085	-0.073	-0.155	0.444	281.032	0.3	-0.707	-0.086	-0.058	-0.196	0.712	263.065	0.4	-0.838	0.621	0.007	0.021	1.043	306.54	0.5	-0.933	-0.216	-0.065	0.103	0.958	256.965	0.6	-0.334	-0.162	-0.129	-0.053	0.371	244.125	0.7	-0.74	0.352	-0.068	-0.046	0.819	295.439	0.8	-0.361	0.469	-0.082	0.062	0.592	322.414	0.9	-0.246	-0.068	-0.192	0.05	0.255	254.548	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A
2	5/16/23 14:43	yellow highlight here	3MHz	HD	1.23	0.02	0.11	0.12	-0.534	0.257	0.009	0.014	0.593	295.7	0.14	-0.545	0.268	0.011	0.019	0.607	296.185	0.16	-0.544	0.257	0.008	0.026	0.602	295.287	0.18	-0.551	0.255	0.007	0.027	0.607	294.834	0.2	-0.519	0.211	0.03	0.066	0.56	292.124	0.22	-0.513	0.202	0.028	0.069	0.551	291.493	0.24	-0.593	0.312	-0.014	0.005	0.67	297.751	0.26	-0.535	0.206	0.014	0.06	0.573	291.059	0.28	-0.575	0.263	0	0.032	0.632	294.579	0.3	-0.549	0.228	0.005	0.031	0.594	292.553	0.32	-0.554	0.224	0.006	0.037	0.598	292.015	0.34	-0.577	0.219	-0.002	0.045	0.617	290.784	0.36	-0.555	0.229	0.004	0.038	0.6	292.422	0.38	-0.531	0.233	0.014	0.023	0.58	293.692	0.4	-0.505	0.276	0.018	-0.016	0.576	298.658	0.42	-0.478	0.215	0.029	0.013	0.524	294.218	0.44	-0.433	0.218	0.04	-0.003	0.485	296.724	0.46	-0.455	0.217	0.032	-0.002	0.504	295.498	0.48	-0.569	0.166	-0.005	0.057	0.593	286.264	0.5	-0.457	0.245	0.031	-0.011	0.519	298.196	0.52	-0.57	0.176	0.003	0.065	0.597	287.159	0.54	-0.459	0.254	0.031	-0.013	0.525	298.959	0.56	-0.452	0.215	0.031	-0.002	0.501	295.439	0.58	-0.562	0.151	0.003	0.068	0.582	285.039	0.6	-0.502	0.3	0.013	-0.05	0.585	300.863	0.62	-0.49	0.258	0.032	-0.032	0.554	297.768	0.64	-0.503	0.255	0.031	-0.035	0.564	296.883	0.66	-0.474	0.264	0.046	-0.007	0.543	299.116	0.68	-0.599	0.208	-0.005	0.046	0.634	289.149	0.7	-0.51	0.31	0.02	-0.029	0.597	301.293	0.72	-0.49	0.32	0.015	-0.005	0.585	303.147	0.74	-0.582	0.276	-0.014	0.016	0.644	295.372	0.76	-0.557	0.271	-0.011	-0.017	0.619	295.945	0.78	-0.551	0.288	-0.016	-0.024	0.622	297.595	0.8	-0.527	0.262	-0.024	-0.015	0.589	296.434	0.82	-0.54	0.186	-0.01	0.06	0.571	289.006	0.84	-0.559	0.184	-0.01	0.034	0.589	288.219	0.86	-0.499	0.181	-0.017	0.045	0.531	289.937	0.88	-0.534	0.324	0.015	0.072	0.625	301.247	0.9	-0.522	0.325	0.013	0.075	0.615	301.907	0.92	-0.447	0.196	-0.002	0.049	0.488	293.676	0.94	-0.476	0.247	-0.018	0.021	0.536	297.425	0.96	-0.519	0.413	0.011	0.041	0.663	308.511	0.98	-0.482	0.389	0.005	-0.012	0.619	308.905	1	-0.487	0.354	0.014	0.018	0.602	306.013	1.02	-0.477	0.316	0.001	-0.029	0.572	303.523	1.04	-0.583	0.379	0.013	0.068	0.695	303.027	1.06	-0.561	0.373	0.008	0.072	0.674	303.619	1.08	-0.666	0.295	0.057	0.036	0.728	293.891	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A

 

[Fluff]

The problem is the cells do not have na, they have #N/A which is a totally different thing.
Try

=TAKE(FILTER(F2:NA2,NOT(ISNA(F2:NA2))),,-1)