Dynamic Range for Match Function

Hello Everyone,

I have an excel file with three sheets as follow, services, parts and invoices
I used the match function on the services sheet to find at which row the invoice of that service starts in the invoices sheet =MATCH("Invoice #8468",INVOICES!A:A,0) and let say it returns
Now what I am trying to do is using another match function but using the value provided in the first match function (82) as the beginning of the lookup array

something like =MATCH("------------Services","INVOICES!A"&L2&":A1000",0)

This is needed in order to find the discount for the services which only appears in the invoices

I have attached a dummy file

You can use this formula in cell M2 of the Services sheet:

=MATCH("------------Services",INDIRECT("Invoices!A"&L2&":A1000"),0)

You should note, though, that MATCH returns the relative position of the match within the range, so if the result is 12 and L2 contains 82, then this refers to row 93 within the INVOICES sheet.

Hope this helps.

Pete

Thank you very much, the formula worked like a charm