Find string, then find immediately below matched string find the word "subtotal" then ...

Hey knowledge well. I've been experimenting with vlookup/hlookups and not really getting far. Just looking for some advice please.

If I have a bunch of order numbers in one sheet, called "orders" and there's no heading, it's simply at A1 an order number exists, of say "P1988", and then another one at A2 "P2042" so on and so forth ...

How do I use that list of order numbers to then go and search across another sheet (in the same spreadsheet) called, "timesheets" to do the following.

Using cell A1 in "orders" lookup that number in column B of "timesheets" (ignoring the first 10 rows) from row 11 down, and when that order number is found, search down in the same column B until you find the word "subtotal", when "Subtotal" is found, move right 11 columns and when you land on that value in column M, copy the value in that cell back to the "orders" sheet in cell B1

so on and so forth, until the end of the list is reached in orders with a corresponding value.

Thanks all, appreciate any pointers.

This would be a lot easier with a sample workbook. But, something like this?

=INDEX(Timesheets!$M$11:$M$100,MATCH("Subtotal",INDEX(Timesheets!$B$11:$B$100,MATCH(A1,Timesheets!$B$11:$B$100,0)):Timesheets!$B$100,0)+MATCH(A1,Timesheets!$B$11:$B$100,0)-1)

Thanks Ali for the reminder, and to Nick for a proposed solution (and so quickly after the posting).

I've uploaded a sample file called "ExampleOrder2.xlsx" with two sheets, I have provided some comments in those sheets too.

Thanks again

Ok, a slight tweak, but try:

=INDEX(timesheet!$L$12:$L$1000,MATCH("Subtotal",INDEX(timesheet!$B$12:$B$1000,MATCH("*"&A2&"*",timesheet!$B$12:$B$1000,0)):timesheet!$B$1000,0)+MATCH("*"&A2&"*",timesheet!$B$12:$B$1000,0)-1)

Oh jeeze, that works thanks so much ...
I'd love to try and break this down a bit. I've probably missed parts, happy to be corrected so I can understand this better.
INDEX (i.e., create an index over the timesheet across the L column down from rows 12 to 1000 MATCH it against
"Subtotal", but create another index of "subtotal" across column B down 1000 rows, MATCH it against
A2 relative, against timesheet column B up to 1000 rows,
but then get a bit lost :P
Cheers though, really appreciate it.

I'll try and explain, hopefully it helps.

Working from the middle out...

MATCH("*"&A2&"*",timesheet!$B$12:$B$1000,0)
Finds the first row where column B contains your job number (e.g. "P1988"). In this case it will return 1 as it's the first row of the range.

INDEX(timesheet!$B$12:$B$1000,MATCH("*"&A2&"*",timesheet!$B$12:$B$1000,0))
Uses this result to define the start of the range to look for "Subtotal". Note you can use index to define the start or end of a range, e.g. INDEX(A:A,1):A5 will resolve as A1:A5

MATCH("Subtotal",INDEX(timesheet!$B$12:$B$1000,MATCH("*"&A2&"*",timesheet!$B$12:$B$1000,0)):timesheet!$B$1000,0)
Finds the first row where column B equals "Subtotal", after finding P1988 as per steps above. It will start counting from the previous result. In this case there's no difference as the first result was 1, but this will return 38.

INDEX(timesheet!$L$12:$L$1000,MATCH("Subtotal",INDEX(timesheet!$B$12:$B$1000,MATCH("*"&A2&"*",timesheet!$B$12:$B$1000,0)):timesheet!$B$1000,0)+MATCH("*"&A2&"*",timesheet!$B$12:$B$1000,0)-1)
Adds the two MATCH results together and subtracts 1 to get the right row reference (38+1-1), then uses that within another INDEX to return the value from column L of this row.