Find match and return adjacent cell

[Chris_1]

Hello,

Sorry I have web version and cannot figure out how to paste actual excel example.
I am trying to figure out how to find the word JOB: and get the result next to it.
I have a constant. It is always on row 3
Problems. Not in a table. It moves columns and needs to be concatenated.

Any suggestions would be much appreciated.

 

[jolivanes]

Finding the Value "JOB:" is no problem but the "result next to it" is.
Is that left ("BILL") of it or right ("BIG") of it?

 

[Chris_1]

Finding the Value "JOB:" is no problem but the "result next to it" is.
Is that left ("BILL") of it or right ("BIG") of

 

[Chris_1]

Sorry. The Yellow is what I am expecting as a result.
"BIG MALL JOB" for cell B5 and B11.

 

[Chris_1]

Sorry. The Yellow is what I am expecting as a result.
"BIG MALL JOB" for cell B5 and B11.

Hah. it says Bill. It is a name.

 

[jolivanes]

Sub Maybe_So()
    MsgBox ActiveSheet.Rows(3).Find("JOB:", , , 1).Offset(, -1).Value
End Sub

Sub Or_Maybe_So()
    MsgBox ActiveSheet.Rows(3).Find("JOB:", , , 1).Offset(, 1).Value
End Sub

Sub Or_Maybe_So_Even()
    MsgBox ActiveSheet.Rows(3).Find("JOB:", , , 1).Offset(1).Value
End Sub

 

[Chris_1]

I'm sorry but I'm not sure how to use VBS on the web version.

 

[jolivanes]

Put this formula in B5 (and B11 maybe)

=R[-2]C[2]&" "&R[-2]C[3]&" "&R[-2]C[4]

I don't know if you can run a macro on a "leased" excel.

If you find out that you can and find out how to install/run a macro, this would give the same result as the formula.

Sub Or_So_Concat()
    With ActiveSheet.Rows(3).Find("JOB:", , , 1)
        .Offset(2, -1).Value = .Offset(, 1).Value & " " & .Offset(, 2).Value & " " & .Offset(, 3).Value
    End With
End Sub

 

[kevin9999]

I'm not sure if I'm misunderstanding your requirement or not (and I'm sure there's a more elegant solution than this) but see if this gets close to what you want:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
3			JOB:	BIG	MALL	JOB
4
5	JOB:	BIG MALL JOB
6
7
8
9				JOB:	BIG	HOUSE	JOB
10
11	JOB:	BIG HOUSE JOB
12
13
14
15								JOB:		small		MALL		JOB
16
17	JOB:	small MALL JOB
18
Sheet1

Cell Formulas
Range	Formula
B5,B17,B11	B5	=IFERROR(TEXTJOIN(" ",TRUE,OFFSET(INDIRECT(CELL("address",INDEX(C3:Z3,MATCH("JOB:",C3:W3,0)))),0,1,1,20),""),"")

 

[Chris_1]

I'm not sure if I'm misunderstanding your requirement or not (and I'm sure there's a more elegant solution than this) but see if this gets close to what you want:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
3			JOB:	BIG	MALL	JOB
4
5	JOB:	BIG MALL JOB
6
7
8
9				JOB:	BIG	HOUSE	JOB
10
11	JOB:	BIG HOUSE JOB
12
13
14
15								JOB:		small		MALL		JOB
16
17	JOB:	small MALL JOB
18
Sheet1

Cell Formulas
Range	Formula
B5,B17,B11	B5	=IFERROR(TEXTJOIN(" ",TRUE,OFFSET(INDIRECT(CELL("address",INDEX(C3:Z3,MATCH("JOB:",C3:W3,0)))),0,1,1,20),""),"")

I dont understand what you have done here yet but the magic works.
I assumed it was an index/match thing but could not figure out how to respond with adjacent cell data.
You even got the concat to work.
What does the "address" in the formula do?
Thank you!