Formula to find text without specifying row or column number

[S Oberlander]

Hi, I need a formula to find the text in cell after a certain text string. However the text string that is my reference point can be in a different column or row in each situation.
To clarify, I have the text "Rate Structure" once in the range A1:Z24, I need the value of the cell immediately to the right of the cell containing "Rate Structure". Is there any way to do this?

 

[AhoyNC]

Here is a formula to try:

Book1
	A	B	C	D	E	F	G	H	I	J															Y	Z
1	148	595	735	637	842	835	620	580	348	169															781	390
2	828	278	886	302	331	792	869	693	913	524															875	982
3	796	718	429	640	711	301	939	307	503	560															495	455
4	375	369	505	715	483	204	379	570	246	701															874	862
5	884	956	Test01	556	209	789	294	446	697	577															709	446
6	665	789	762	139	988	627	718	580	395	769															258	346
7	628	522	313	746	317	713	664	357	Test22	928															757	507
8	533	253	654	763	143	627	253	259	679	217															471	666
9	538	828	917	277	316	767	949	662	842	847															786	151
10	859	738	475	220	291	595	399	149	816	944															414	808
11	969	635	917	212	509	573	497	533	797	240															423	357
12	225	331	802	520	Test02	596	578	854	961	163															414	895
13	217	103	242	729	150	618	433	806	641	211															226	587
14	255	871	709	827	847	208	700	372	553	186															453	485
15	495	621	753	362	294	341	Rate Structure	352	541	501															372	549
16	156	913	873	391	765	255	991	602	679	849															271	488
17	164	911	207	745	158	803	767	795	242	209															300	325
18	440	302	891	885	968	665	247	229	449	222															331	909
19	138	397	585	836	259	471	922	161	966	196															236	814
20	880	873	334	256	619	238	324	695	743	674															761	654
21	600	193	816	840	965	717	633	224	616	103															468	637
22	496	512	103	756	713	802	574	869	601	818															726	961
23	588	595	142	440	114	322	649	919	798	546															262	556
24	645	438	608	524	734	727	411	929	887	764															320	775
25
26	352
Sheet3

Cell Formulas
Range	Formula
A26	A26	=INDEX($A$1:$Z$24,AGGREGATE(14,6,(ROW($A$1:$A$24)-ROW($A$1)+1)/(SEARCH("Rate Structure",$A$1:$Z$24)),1),AGGREGATE(14,6,(COLUMN($A$1:$Z$1)-COLUMN($A$1)+1)/(SEARCH("Rate Structure",$A$1:$Z$24)),1)+1)

 

[S Oberlander]

For further explanation, the "Rate structure" is placed

[TD][/TD]
[TD]=INDEX($A$1:$Z$24,AGGREGATE(14,6,(ROW($A$1:$A$24)-ROW($A$1)+1)/(SEARCH("Rate Structure",$A$1:$Z$24)),1),AGGREGATE(14,6,(COLUMN($A$1:$Z$1)-COLUMN($A$1)+1)/(SEARCH("Rate Structure",$A$1:$Z$24)),1)+1)[/TD]

WOW! I love it! This is getting integrated with a formula that's already longer then this...

 

[Peter_SSs]

Welcome to the MrExcel board!

I have the text "Rate Structure" once in the range A1:Z24

If that is so then perhaps some shorter alternatives can be used.
- if the value to the right of "Rate Structure" is known to be numeric then you could try the formula in A26
- otherwise try A27

@AhoyNC
Are you aware that there have been several new versions of XL2BB that have addressed quite a few bugs as well as enhancements?

20 11 26.xlsm
	A	B	C	D	E	F	G	H	I	J															Y	Z	AA
1	148	595	735	637	842	835	620	580	348	169															781	390	443
2	828	278	886	302	331	792	869	693	913	524															875	982	184
3	796	718	429	640	711	301	939	307	503	560															495	455	485
4	375	369	505	715	483	204	379	570	246	701															874	862	982
5	884	956	Test01	556	209	789	294	446	697	577															709	446	854
6	665	789	762	139	988	627	718	580	395	769															258	346	978
7	628	522	313	746	317	713	664	357	Test22	928															757	507	298
8	533	253	654	763	143	627	253	259	679	217															471	666	787
9	538	828	917	277	316	767	949	662	842	847															786	151	542
10	859	738	475	220	291	595	399	149	816	944															414	808	589
11	969	635	917	212	509	573	497	533	797	240															423	357	619
12	225	331	802	520	Test02	596	578	854	961	163															414	895	338
13	217	103	242	729	150	618	433	806	641	211															226	587	707
14	255	871	709	827	847	208	700	372	553	186															453	485	438
15	495	621	753	362	294	341	Rate Structure	352	541	501															372	549	145
16	156	913	873	391	765	255	991	602	679	849															271	488	577
17	164	911	207	745	158	803	767	795	242	209															300	325	451
18	440	302	891	885	968	665	247	229	449	222															331	909	331
19	138	397	585	836	259	471	922	161	966	196															236	814	859
20	880	873	334	256	619	238	324	695	743	674															761	654	181
21	600	193	816	840	965	717	633	224	616	103															468	637	651
22	496	512	103	756	713	802	574	869	601	818															726	961	153
23	588	595	142	440	114	322	649	919	798	546															262	556	884
24	645	438	608	524	734	727	411	929	887	764															320	775	230
25
26	352
27	352
Rate Structure

Cell Formulas
Range	Formula
A26	A26	=SUMPRODUCT(--(B1:Z24="Rate Structure"),C1:AA24)
A27	A27	=INDEX(B1:AA24,SUMPRODUCT((A1:Z24="Rate Structure")*ROW(A1:Z24))-ROW(A1)+1,SUMPRODUCT((A1:Z24="Rate Structure")*COLUMN(A1:Z24))-COLUMN(A1)+1)

 

[S Oberlander]

Welcome to the MrExcel board!


If that is so then perhaps some shorter alternatives can be used.
- if the value to the right of "Rate Structure" is known to be numeric then you could try the formula in A26
- otherwise try A27

@AhoyNC
Are you aware that there have been several new versions of XL2BB that have addressed quite a few bugs as well as enhancements?

20 11 26.xlsm
	A	B	C	D	E	F	G	H	I	J															Y	Z	AA
1	148	595	735	637	842	835	620	580	348	169															781	390	443
2	828	278	886	302	331	792	869	693	913	524															875	982	184
3	796	718	429	640	711	301	939	307	503	560															495	455	485
4	375	369	505	715	483	204	379	570	246	701															874	862	982
5	884	956	Test01	556	209	789	294	446	697	577															709	446	854
6	665	789	762	139	988	627	718	580	395	769															258	346	978
7	628	522	313	746	317	713	664	357	Test22	928															757	507	298
8	533	253	654	763	143	627	253	259	679	217															471	666	787
9	538	828	917	277	316	767	949	662	842	847															786	151	542
10	859	738	475	220	291	595	399	149	816	944															414	808	589
11	969	635	917	212	509	573	497	533	797	240															423	357	619
12	225	331	802	520	Test02	596	578	854	961	163															414	895	338
13	217	103	242	729	150	618	433	806	641	211															226	587	707
14	255	871	709	827	847	208	700	372	553	186															453	485	438
15	495	621	753	362	294	341	Rate Structure	352	541	501															372	549	145
16	156	913	873	391	765	255	991	602	679	849															271	488	577
17	164	911	207	745	158	803	767	795	242	209															300	325	451
18	440	302	891	885	968	665	247	229	449	222															331	909	331
19	138	397	585	836	259	471	922	161	966	196															236	814	859
20	880	873	334	256	619	238	324	695	743	674															761	654	181
21	600	193	816	840	965	717	633	224	616	103															468	637	651
22	496	512	103	756	713	802	574	869	601	818															726	961	153
23	588	595	142	440	114	322	649	919	798	546															262	556	884
24	645	438	608	524	734	727	411	929	887	764															320	775	230
25
26	352
27	352
Rate Structure

Cell Formulas
Range	Formula
A26	A26	=SUMPRODUCT(--(B1:Z24="Rate Structure"),C1:AA24)
A27	A27	=INDEX(B1:AA24,SUMPRODUCT((A1:Z24="Rate Structure")*ROW(A1:Z24))-ROW(A1)+1,SUMPRODUCT((A1:Z24="Rate Structure")*COLUMN(A1:Z24))-COLUMN(A1)+1)

Nope, the value to the right is also text

 

[Peter_SSs]

Nope, the value to the right is also text

Than, as I said ...

- otherwise try A27

20 11 26.xlsm
	A	B	C	D	E	F	G	H	I	J															Y	Z	AA	AB
1	148	595	735	637	842	835	620	580	348	169															781	390	443
2	828	278	886	302	331	792	869	693	913	524															875	982	184
3	796	718	429	640	711	301	939	307	503	560															495	455	485
4	375	369	505	715	483	204	379	570	246	701															874	862	982
5	884	956	Test01	556	209	789	294	446	697	577															709	446	854
6	665	789	762	139	988	627	718	580	395	769															258	346	978
7	628	522	313	746	317	713	664	357	Test22	928															757	507	298
8	533	253	654	763	143	627	253	259	679	217															471	666	787
9	538	828	917	277	316	767	949	662	842	847															786	151	542
10	859	738	475	220	291	595	399	149	816	944															414	808	589
11	969	635	917	212	509	573	497	533	797	240															423	357	619
12	225	331	802	520	Test02	596	578	854	961	163															414	895	338
13	217	103	242	729	150	618	433	806	641	211															226	587	707
14	255	871	709	827	847	208	700	372	553	186															453	485	438
15	495	621	753	362	294	341	Rate Structure	abcd	541	501															372	549	145
16	156	913	873	391	765	255	991	602	679	849															271	488	577
17	164	911	207	745	158	803	767	795	242	209															300	325	451
18	440	302	891	885	968	665	247	229	449	222															331	909	331
19	138	397	585	836	259	471	922	161	966	196															236	814	859
20	880	873	334	256	619	238	324	695	743	674															761	654	181
21	600	193	816	840	965	717	633	224	616	103															468	637	651
22	496	512	103	756	713	802	574	869	601	818															726	961	153
23	588	595	142	440	114	322	649	919	798	546															262	556	884
24	645	438	608	524	734	727	411	929	887	764															320	775	230
25
26
27	abcd
Rate Structure

Cell Formulas
Range	Formula
A27	A27	=INDEX(B1:AA24,SUMPRODUCT((A1:Z24="Rate Structure")*ROW(A1:Z24))-ROW(A1)+1,SUMPRODUCT((A1:Z24="Rate Structure")*COLUMN(A1:Z24))-COLUMN(A1)+1)

 

[AhoyNC]

@Peter_SSs - I have updated to the new version of XL2BB. I was aware of the newer version, but my old version was working for me. Seems like when I uninstall and reinstall an app that should go quickly ends up taking 1/2 day.

 

[Peter_SSs]

Seems like when I uninstall and reinstall an app that should go quickly ends up taking 1/2 day.

Hmm, I don't know what must have happened there? There have been several versions and the update process each time has been very fast for me.

 

[AhoyNC]

@ Peter_SSs - No this update went easy. In the past I've had issues with other add ins/updates that didn't go as smooth.

 

[Peter_SSs]

@ Peter_SSs - No this update went easy.

Ah .. sorry, I misunderstood.