Formula to find next instance of a word in a specific column?

[AGrayson84]

Hi everyone, I was hoping someone may be able to assist me with an unusual request that I have not been able to find a formula for that I have been able to modify successfully. I have a worksheet that contains the many instances of the word "Subnet" within column "D". For each instance of "Subnet" in column D, there is one instance of the word "Gateway" in column D of a below row. Below is a very rough example:

[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]sdsad[/TD]
[TD]sadas[/TD]
[TD]dfdsf[/TD]
[TD]subnet[/TD]
[/TR]
[TR]
[TD]dfsdfsd[/TD]
[TD]dfds[/TD]
[TD]sadfas[/TD]
[TD]gateway[/TD]
[/TR]
[TR]
[TD]dfsdf[/TD]
[TD]dsfsdf[/TD]
[TD]dsfsd[/TD]
[TD]sdfsf[/TD]
[/TR]
[TR]
[TD]dfsdf[/TD]
[TD]dsfsdf[/TD]
[TD]dsfs[/TD]
[TD]subnet
[/TD]
[/TR]
[TR]
[TD]sdfgs[/TD]
[TD]sdgdh[/TD]
[TD]sdgsdf[/TD]
[TD]dfgfdgh[/TD]
[/TR]
[TR]
[TD]dgsfdgfd[/TD]
[TD]sfsd[/TD]
[TD]dsfsdg[/TD]
[TD]dgfdg[/TD]
[/TR]
[TR]
[TD]fsfgf[/TD]
[TD]gdfgfd[/TD]
[TD]sdgsrr[/TD]
[TD]gateway[/TD]
[/TR]
[TR]
[TD]hfdhggf[/TD]
[TD]sfgfd[/TD]
[TD]dfgsdfg[/TD]
[TD]subnet[/TD]
[/TR]
[TR]
[TD]gsdfgf[/TD]
[TD]sdfsd[/TD]
[TD]fgsdgd[/TD]
[TD]gateway[/TD]
[/TR]
</tbody>[/TABLE]



What I would like to do is have a formula that I can use in column E of each row where "subnet" is in column D, and I would like that formula to find the next instance of "gateway" in column D (it's not always in the very-next row), then write the value of column A of that same row (the "gateway" row) into column E of the subnet row. For instance:

[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]sdsad[/TD]
[TD]sadas[/TD]
[TD]dfdsf[/TD]
[TD]subnet[/TD]
[TD]dfsdfsd[/TD]
[/TR]
[TR]
[TD]dfsdfsd[/TD]
[TD]dfds[/TD]
[TD]sadfas[/TD]
[TD]gateway[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]dfsdf[/TD]
[TD]dsfsdf[/TD]
[TD]dsfsd[/TD]
[TD]sdfsf[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]dfsdf[/TD]
[TD]dsfsdf[/TD]
[TD]dsfs[/TD]
[TD]subnet[/TD]
[TD]fsfgf[/TD]
[/TR]
[TR]
[TD]sdfgs[/TD]
[TD]sdgdh[/TD]
[TD]sdgsdf[/TD]
[TD]dfgfdgh[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]dgsfdgfd[/TD]
[TD]sfsd[/TD]
[TD]dsfsdg[/TD]
[TD]dgfdg[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]fsfgf[/TD]
[TD]gdfgfd[/TD]
[TD]sdgsrr[/TD]
[TD]gateway[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]hfdhggf[/TD]
[TD]sfgfd[/TD]
[TD]dfgsdfg[/TD]
[TD]subnet[/TD]
[TD]gsdfgf[/TD]
[/TR]
[TR]
[TD]gsdfgf[/TD]
[TD]sdfsd[/TD]
[TD]fgsdgd[/TD]
[TD]gateway[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I believe I do have a VBA Script I am using for something else, that I can easily modify to use for placing the formula for each cell in column "E" where the word "subnet" is found in Column "D". However, Macros are disabled on most of our workstations on the specific domain that this spreadsheet is on, so if there is a way to do it without macro that would be great. But I primarily need just the formula for finding the value of the next instance of the cell in column A, where "gateway" is found in column D of the same row.

Hopefully that's not too confusing and if anyone needs to me to elaborate on anything please let me know. Any help on this would be hugely appreciated! Thanks everyone!

-Andrew

 

[wideboydixon]

Book1
	A	B	C	D	E
1	sdsad	sadas	dfdsf	subnet	dfsdfsd
2	dfsdfsd	dfds	sadfas	gateway
3	dfsdf	dsfsdf	dsfsd	sdfsf
4	dfsdf	dsfsdf	dsfs	subnet	fsfgf
5	sdfgs	sdgdh	sdgsdf	dfgfdgh
6	dgsfdgfd	sfsd	dsfsdg	dgfdg
7	fsfgf	gdfgfd	sdgsrr	gateway
8	hfdhggf	sfgfd	dfgsdfg	subnet	gsdfgf
9	gsdfgf	sdfsd	fgsdgd	gateway
Sheet1

Cell Formulas
Range	Formula
E1	{=IF($D1="subnet",INDEX($A2:$A$9,MIN(IF($D2:$D$9="gateway",ROW($D2:$D$9)-ROW($D2)+1))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

WBD

 

[Marcelo Branco]

Or

E1 copied down
=IF(D1="subnet",INDEX(A1:$A$1000,MATCH("gateway",D1:$D$1000,0)),"")

M.

 

[AGrayson84]

Thank you both so much for the help!!!!!!!!!!!!!!

	A	B	C	D	E
sdsad	sadas	dfdsf	subnet	dfsdfsd
dfsdfsd	dfds	sadfas	gateway
dfsdf	dsfsdf	dsfsd	sdfsf
dfsdf	dsfsdf	dsfs	subnet	fsfgf
sdfgs	sdgdh	sdgsdf	dfgfdgh
dgsfdgfd	sfsd	dsfsdg	dgfdg
fsfgf	gdfgfd	sdgsrr	gateway
hfdhggf	sfgfd	dfgsdfg	subnet	gsdfgf
gsdfgf	sdfsd	fgsdgd	gateway

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]9[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E1[/TH]
[TD="align: left"]{=IF($D1="subnet",INDEX($A2:$A$9,MIN(IF($D2:$D$9="gateway",ROW($D2:$D$9)-ROW($D2)+1))),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself
[/TD]
[/TR]
</tbody>[/TABLE]



WBD

Thank you WBD! I did try this and did the CTRL + SHIFT + ENTER method. It did populate the brackets for me when I did that, but the cell I entered it in had no data. Might have been something on my part that caused it not to work though!

Or

E1 copied down
=IF(D1="subnet",INDEX(A1:$A$1000,MATCH("gateway",D1:$D$1000,0)),"")

M.

Thanks M! I wasn't hoping to fill a formula in all cells of column D to make this work, but surely it was probably the only option to do it without macros, so I made a new column for this formula anyway and it happened to work out just fine




Thanks again to both of you!!!!!

-Andrew

 

[Marcelo Branco]

You are welcome. Glad to help.

M.