Find cell reference based on text value across a row

S

Swaroon

Active Member
Joined
Nov 18, 2005
Messages
288
Office Version
  1. 365
Hi,

Each row in my spreadsheet may contain cells that contain the text "TD CONTRACT" & a reference eg.

Row 15: cell D15 = TD CONTRACT 12345, cell H15 = TD CONTRACT 22345, cell K15 = TD CONTRACT 32345 etc,
Row 16: cell M16 = TD CONTRACT 42345, cell R16 = TD CONTRACT 52345, cell AA16 = TD CONTRACT 62345 etc,

I need to show the cell reference in cell CA15 for the first occurance of finding the text value of "TD CONTRACT", ie excluding the reference in a range A15:BZ15
Would then copy the formula from CA15 to CA16 to pull cell value for row 16 etc.


Thanks
Steve
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Sort by date Sort by votes
Older Excel formula to drag down:
Book1
ABCDEFG
1TD CONTRACT 1111TD CONTRACT 1112TD CONTRACT 1113TD CONTRACT 1114TD CONTRACT 1115TD CONTRACT 1116$A$1
2TD CONTRACT 1111TD CONTRACT 1112TD CONTRACT 1113TD CONTRACT 1114TD CONTRACT 1115TD CONTRACT 1116$A$2
3sTD CONTRACT 1112TD CONTRACT 1113TD CONTRACT 1114TD CONTRACT 1115TD CONTRACT 1116$B$3
4TD CONTRACT 1111TD CONTRACT 1112TD CONTRACT 1113TD CONTRACT 1114TD CONTRACT 1115TD CONTRACT 1116$A$4
5ssssTD CONTRACT 1115TD CONTRACT 1116$E$5
6TD CONTRACT 1111TD CONTRACT 1112TD CONTRACT 1113TD CONTRACT 1114TD CONTRACT 1115TD CONTRACT 1116$A$6
7ssTD CONTRACT 1113TD CONTRACT 1114TD CONTRACT 1115TD CONTRACT 1116$C$7
8TD CONTRACT 1111TD CONTRACT 1112TD CONTRACT 1113TD CONTRACT 1114TD CONTRACT 1115TD CONTRACT 1116$A$8
9ssTD CONTRACT 1113ssTD CONTRACT 1116$C$9
10TD CONTRACT 1111TD CONTRACT 1112TD CONTRACT 1113TD CONTRACT 1114TD CONTRACT 1115TD CONTRACT 1116$A$10
11sssTD CONTRACT 1114TD CONTRACT 1115TD CONTRACT 1116$D$11
12sssssTD CONTRACT 1116$F$12
Sheet1
Cell Formulas
RangeFormula
G1:G12G1=ADDRESS(ROW(), MATCH("TD CONTRACT*",A1:F1,0))


Excel 365 formula BYROW:
Book1
ABCDEFG
15TD CONTRACT 1111TD CONTRACT 1112TD CONTRACT 1113TD CONTRACT 1114TD CONTRACT 1115TD CONTRACT 1116$A$15
16TD CONTRACT 1111TD CONTRACT 1112TD CONTRACT 1113TD CONTRACT 1114TD CONTRACT 1115TD CONTRACT 1116$A$16
17sTD CONTRACT 1112TD CONTRACT 1113TD CONTRACT 1114TD CONTRACT 1115TD CONTRACT 1116$B$17
18TD CONTRACT 1111TD CONTRACT 1112TD CONTRACT 1113TD CONTRACT 1114TD CONTRACT 1115TD CONTRACT 1116$A$18
19ssssTD CONTRACT 1115TD CONTRACT 1116$E$19
20TD CONTRACT 1111TD CONTRACT 1112TD CONTRACT 1113TD CONTRACT 1114TD CONTRACT 1115TD CONTRACT 1116$A$20
21ssTD CONTRACT 1113TD CONTRACT 1114TD CONTRACT 1115TD CONTRACT 1116$C$21
22TD CONTRACT 1111TD CONTRACT 1112TD CONTRACT 1113TD CONTRACT 1114TD CONTRACT 1115TD CONTRACT 1116$A$22
23ssTD CONTRACT 1113ssTD CONTRACT 1116$C$23
24TD CONTRACT 1111TD CONTRACT 1112TD CONTRACT 1113TD CONTRACT 1114TD CONTRACT 1115TD CONTRACT 1116$A$24
25sssTD CONTRACT 1114TD CONTRACT 1115TD CONTRACT 1116$D$25
26sssssTD CONTRACT 1116$F$26
Sheet1
Cell Formulas
RangeFormula
G15:G26G15=BYROW(A15:F26,LAMBDA(x,ADDRESS(ROW(x), MATCH("TD CONTRACT*",x,0))))
Dynamic array formulas.
 
Upvote 0
What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
 
Upvote 0
Georgiboy said:
Older Excel formula to drag down:
Book1
ABCDEFG
1TD CONTRACT 1111TD CONTRACT 1112TD CONTRACT 1113TD CONTRACT 1114TD CONTRACT 1115TD CONTRACT 1116$A$1
2TD CONTRACT 1111TD CONTRACT 1112TD CONTRACT 1113TD CONTRACT 1114TD CONTRACT 1115TD CONTRACT 1116$A$2
3sTD CONTRACT 1112TD CONTRACT 1113TD CONTRACT 1114TD CONTRACT 1115TD CONTRACT 1116$B$3
4TD CONTRACT 1111TD CONTRACT 1112TD CONTRACT 1113TD CONTRACT 1114TD CONTRACT 1115TD CONTRACT 1116$A$4
5ssssTD CONTRACT 1115TD CONTRACT 1116$E$5
6TD CONTRACT 1111TD CONTRACT 1112TD CONTRACT 1113TD CONTRACT 1114TD CONTRACT 1115TD CONTRACT 1116$A$6
7ssTD CONTRACT 1113TD CONTRACT 1114TD CONTRACT 1115TD CONTRACT 1116$C$7
8TD CONTRACT 1111TD CONTRACT 1112TD CONTRACT 1113TD CONTRACT 1114TD CONTRACT 1115TD CONTRACT 1116$A$8
9ssTD CONTRACT 1113ssTD CONTRACT 1116$C$9
10TD CONTRACT 1111TD CONTRACT 1112TD CONTRACT 1113TD CONTRACT 1114TD CONTRACT 1115TD CONTRACT 1116$A$10
11sssTD CONTRACT 1114TD CONTRACT 1115TD CONTRACT 1116$D$11
12sssssTD CONTRACT 1116$F$12
Sheet1
Cell Formulas
RangeFormula
G1:G12G1=ADDRESS(ROW(), MATCH("TD CONTRACT*",A1:F1,0))


Excel 365 formula BYROW:
Book1
ABCDEFG
15TD CONTRACT 1111TD CONTRACT 1112TD CONTRACT 1113TD CONTRACT 1114TD CONTRACT 1115TD CONTRACT 1116$A$15
16TD CONTRACT 1111TD CONTRACT 1112TD CONTRACT 1113TD CONTRACT 1114TD CONTRACT 1115TD CONTRACT 1116$A$16
17sTD CONTRACT 1112TD CONTRACT 1113TD CONTRACT 1114TD CONTRACT 1115TD CONTRACT 1116$B$17
18TD CONTRACT 1111TD CONTRACT 1112TD CONTRACT 1113TD CONTRACT 1114TD CONTRACT 1115TD CONTRACT 1116$A$18
19ssssTD CONTRACT 1115TD CONTRACT 1116$E$19
20TD CONTRACT 1111TD CONTRACT 1112TD CONTRACT 1113TD CONTRACT 1114TD CONTRACT 1115TD CONTRACT 1116$A$20
21ssTD CONTRACT 1113TD CONTRACT 1114TD CONTRACT 1115TD CONTRACT 1116$C$21
22TD CONTRACT 1111TD CONTRACT 1112TD CONTRACT 1113TD CONTRACT 1114TD CONTRACT 1115TD CONTRACT 1116$A$22
23ssTD CONTRACT 1113ssTD CONTRACT 1116$C$23
24TD CONTRACT 1111TD CONTRACT 1112TD CONTRACT 1113TD CONTRACT 1114TD CONTRACT 1115TD CONTRACT 1116$A$24
25sssTD CONTRACT 1114TD CONTRACT 1115TD CONTRACT 1116$D$25
26sssssTD CONTRACT 1116$F$26
Sheet1
Cell Formulas
RangeFormula
G15:G26G15=BYROW(A15:F26,LAMBDA(x,ADDRESS(ROW(x), MATCH("TD CONTRACT*",x,0))))
Dynamic array formulas.
Click to expand...
Thanks for your help, greatly appreciated
 
Upvote 0
Fluff said:
What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
Click to expand...
Will update, thanks for the advice
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Find Text in a cell
Replies
5
Views
160
Fluff
Fluff
I
  • Question
Find last row of sheet based off cell value to reference the sheet name
Replies
0
Views
219
ItalianPlatinum
I
R
  • Solved
Add columns based on a cell value in the row
Replies
3
Views
165
RickT0962
R
A
  • Solved
Comparing two excel files for matching / mismatching rows (long text strings - match() wont work
Replies
4
Views
598
ahaye
A
A
  • Solved
Finding a unique value in a range and returning the value in another column of the same row, without offset / slow formulae.
Replies
8
Views
1K
AaronPMH
A

Forum statistics

Threads
1,215,473
Messages
6,125,018
Members
449,203
Latest member
tungnmqn90

Share this page

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
Back
Top