Formula to find text without specifying row or column number

S Oberlander

Board Regular
Joined
Nov 25, 2020
Messages
60
Office Version
  1. 365
Platform
  1. Windows
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?
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,870
Office Version
  1. 365
Platform
  1. Windows
Here is a formula to try:

Book1
ABCDEFGHIJYZ
1148595735637842835620580348169781390
2828278886302331792869693913524875982
3796718429640711301939307503560495455
4375369505715483204379570246701874862
5884956Test01556209789294446697577709446
6665789762139988627718580395769258346
7628522313746317713664357Test22928757507
8533253654763143627253259679217471666
9538828917277316767949662842847786151
10859738475220291595399149816944414808
11969635917212509573497533797240423357
12225331802520Test02596578854961163414895
13217103242729150618433806641211226587
14255871709827847208700372553186453485
15495621753362294341Rate Structure352541501372549
16156913873391765255991602679849271488
17164911207745158803767795242209300325
18440302891885968665247229449222331909
19138397585836259471922161966196236814
20880873334256619238324695743674761654
21600193816840965717633224616103468637
22496512103756713802574869601818726961
23588595142440114322649919798546262556
24645438608524734727411929887764320775
25
26352
Sheet3
Cell Formulas
RangeFormula
A26A26=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)
 
Solution

S Oberlander

Board Regular
Joined
Nov 25, 2020
Messages
60
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,757
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHIJYZAA
1148595735637842835620580348169781390443
2828278886302331792869693913524875982184
3796718429640711301939307503560495455485
4375369505715483204379570246701874862982
5884956Test01556209789294446697577709446854
6665789762139988627718580395769258346978
7628522313746317713664357Test22928757507298
8533253654763143627253259679217471666787
9538828917277316767949662842847786151542
10859738475220291595399149816944414808589
11969635917212509573497533797240423357619
12225331802520Test02596578854961163414895338
13217103242729150618433806641211226587707
14255871709827847208700372553186453485438
15495621753362294341Rate Structure352541501372549145
16156913873391765255991602679849271488577
17164911207745158803767795242209300325451
18440302891885968665247229449222331909331
19138397585836259471922161966196236814859
20880873334256619238324695743674761654181
21600193816840965717633224616103468637651
22496512103756713802574869601818726961153
23588595142440114322649919798546262556884
24645438608524734727411929887764320775230
25
26352
27352
Rate Structure
Cell Formulas
RangeFormula
A26A26=SUMPRODUCT(--(B1:Z24="Rate Structure"),C1:AA24)
A27A27=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

Board Regular
Joined
Nov 25, 2020
Messages
60
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
ABCDEFGHIJYZAA
1148595735637842835620580348169781390443
2828278886302331792869693913524875982184
3796718429640711301939307503560495455485
4375369505715483204379570246701874862982
5884956Test01556209789294446697577709446854
6665789762139988627718580395769258346978
7628522313746317713664357Test22928757507298
8533253654763143627253259679217471666787
9538828917277316767949662842847786151542
10859738475220291595399149816944414808589
11969635917212509573497533797240423357619
12225331802520Test02596578854961163414895338
13217103242729150618433806641211226587707
14255871709827847208700372553186453485438
15495621753362294341Rate Structure352541501372549145
16156913873391765255991602679849271488577
17164911207745158803767795242209300325451
18440302891885968665247229449222331909331
19138397585836259471922161966196236814859
20880873334256619238324695743674761654181
21600193816840965717633224616103468637651
22496512103756713802574869601818726961153
23588595142440114322649919798546262556884
24645438608524734727411929887764320775230
25
26352
27352
Rate Structure
Cell Formulas
RangeFormula
A26A26=SUMPRODUCT(--(B1:Z24="Rate Structure"),C1:AA24)
A27A27=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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,757
Office Version
  1. 365
Platform
  1. Windows
Nope, the value to the right is also text
Than, as I said ...
- otherwise try A27

20 11 26.xlsm
ABCDEFGHIJYZAAAB
1148595735637842835620580348169781390443
2828278886302331792869693913524875982184
3796718429640711301939307503560495455485
4375369505715483204379570246701874862982
5884956Test01556209789294446697577709446854
6665789762139988627718580395769258346978
7628522313746317713664357Test22928757507298
8533253654763143627253259679217471666787
9538828917277316767949662842847786151542
10859738475220291595399149816944414808589
11969635917212509573497533797240423357619
12225331802520Test02596578854961163414895338
13217103242729150618433806641211226587707
14255871709827847208700372553186453485438
15495621753362294341Rate Structureabcd541501372549145
16156913873391765255991602679849271488577
17164911207745158803767795242209300325451
18440302891885968665247229449222331909331
19138397585836259471922161966196236814859
20880873334256619238324695743674761654181
21600193816840965717633224616103468637651
22496512103756713802574869601818726961153
23588595142440114322649919798546262556884
24645438608524734727411929887764320775230
25
26
27abcd
Rate Structure
Cell Formulas
RangeFormula
A27A27=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

Well-known Member
Joined
Oct 10, 2011
Messages
4,870
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

@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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,757
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Oct 10, 2011
Messages
4,870
Office Version
  1. 365
Platform
  1. Windows
@ 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.
 

Forum statistics

Threads
1,147,814
Messages
5,743,360
Members
423,790
Latest member
kevinlee_5

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top