Search row for certain cell with text and copy that data from below that cell to another sheet

[theowi]

Hello Hey

I'm looking for help on how i can get data from a cell that's sometimes not in the same cell sometimes move to a different one and then i want the data that's below that data cell to be copied over to another sheet. Looking for a formula to do that.

Example

Post Time	DC Desp	Route	Stop
13 -- 13:30	14 -- 04:43	6168	1
13 -- 13:30	14 -- 04:47	6169	1
13 -- 13:30	14 -- 04:53	6170	2

Looking for the data that's below "Route" Title to appear on a different sheet but sometimes that data with the "Route" title is on a different place so i don't want to change the formula every time.

 

[Peter_SSs]

Welcome to the MrExcel board!

Is this what you mean?

theowi 2020-03-14 1.xlsm
	A	B	C	D
1	Post Time	DC Desp	Route	Stop
2	13 -- 13:30	14 -- 04:43	6168	1
3	13 -- 13:30	14 -- 04:47	6169	1
4	13 -- 13:30	14 -- 04:53	6170	2
Sheet1

If using Excel 365, which I note you have, and your particular version has the FILTER function, you could try the formula in A1 below. It should automatically 'spill' the other results down the column.

For all versions, you could try the formula in C1 below and copy down.

theowi 2020-03-14 1.xlsm
	A	B	C
1	Route		Route
2	6168		6168
3	6169		6169
4	6170		6170
5
Sheet2

Cell Formulas
Range	Formula
A1:A4	A1	=FILTER(INDEX(Sheet1!A1:Z10,0,MATCH("Route",Sheet1!1:1,0)),INDEX(Sheet1!A1:Z10,0,MATCH("Route",Sheet1!1:1,0))<>"")
C1:C5	C1	=IFERROR(INDEX(Sheet1!A$1:D$4,ROWS(C$1:C1),MATCH("Route",Sheet1!$1:$1,0)),"")
Dynamic array formulas.

 

[theowi]

Hey Peter...

Someone i am struggling... Sorry for not giving you enough proper information..

So its like this so i changed the formula trying to make it work but didn't not sure what i am doing wrong

=IFERROR(INDEX(Plan_Import!R$1:U$4,ROWS(S$2:S2),MATCH("Route",!$2:$2,0)),"")

So the part in blue will be changing because "Route" wont always be in the column. Changes daily (Sometimes its in Column R then another day in column T)

The sheet the data range is "Plan_Import" and the sheet I'm working from is "Import2"

Oh yes i am using Google Sheets

 

[Peter_SSs]

Oh yes i am using Google Sheets

In that case, I am unable to help as I only deal here with Excel questions.

Also, since this was posted in the Excel Questions forum, readers would be expecting the question to be about Excel. So I am also moving your thread to the
General Excel Discussion & Other Questions forum as its subtitle states "This forum is for Excel-related discussion and questions concerning programs other than Excel or Access. "