Both of the codes below are doing the same procedure, in trying to find a corresponding result to your specified lookup value. As an example, I have picked out the maximum price from historical data, and now I want to find out what quarter of the year corresponds to that specified maximum price.

Using Lookup:
=LOOKUP(L4,INDIRECT(CONCATENATE("'",$D4,"'!G$151:G$163")),INDIRECT(CONCATENATE("'",$D4,"'!B$151:B$163")))

Using Index/Match:
=INDEX(INDIRECT(CONCATENATE("'",$D4,"'!B$151:B$163")), MATCH(L4, INDIRECT(CONCATENATE("'",$D4,"'!G$151:G$163"))))

Where L4 is the lookup value I am trying to find in the historical data (the specified maximum price).
D4 contains the name of the worksheet where the historical data exists.
G151:G163 contains the prices where L4 was originally derived from.
B151:B163 is the list that corresponds to G151:G163, and what I want my output to be (eg. Quarter of the year).

I have to you the indirect since I have data on multiple worksheets that have the same template. So I can use the range 151:163 on every worksheet, but simply need the worksheet name (D4) to change.

Troubleshooting:

I am led to believe that this issue is cause by the Indirect function and its volatile characteristic.

With some worksheets/data, this formula works just fine. With other ones, I get mistakes with the formula. So even though my maximum price is located in G151 for example, and should get the value returned from B151, I instead get the value of B163. Which in the way that Lookup works, this basically means, my maximum price was never found at all. (Right?)

Decomposing my formulas, I find that problem in my Index/Match formula, actually occurs in the Match component. So, =MATCH(L4, INDIRECT(CONCATENATE("'",$D4,"'!G$151:G$163")), returns the last value again, of G163, when in fact it should be G151.

On occasion, with no change to the formula, it sometimes results in #N/A. This may be a related or isolated issue - I don't know.

I can confirm that the value in L4 and G151 are identical through a boolean test.

What boggles me is that it works for about half of my worksheets but not the other half. At this point, I've ran out of debugging ideas!