Search for a text and then search for another text after the row containing the first text

There is probably a more elegant solution, but try this:

Formula:

=MATCH("Product 8",OFFSET(A1:A10000,MATCH("Location: KL",A:A,0),0),0)+MATCH("Location: KL",A:A,0)