Create Lookup to Variable Sheet Name and Dynamic Cell Reference Based on 2 Drop Down Validations

cpatomba 

Restructuring your data for analysis is a good option.

Given what you have, traditional Excel is likely to produce an error-prone mess.  Both Excel 365 and Power Query offer sensible ways forward.

For 365, I would identify the data for each State with a sheet scoped defined name.  The task of the INDIRECT formula is then to return data for the State using a fully qualified name.

= LET(
   stateData, INDIRECT(state&"!data"),
   n, XMATCH(Product, stateData),
   IFERROR(INDEX(stateData, n+{2;3;4}), {"Not stocked";"";""})
  )

The picture also shows a product list for a validation dropdown