Need formula to find correct worksheet and column from text label

Just logged on, and appreciate the input from everyone trying to help me resolve this.

If you look at the v2 file I attached, Col P of the ‘Comparisons’ sheet (Mar-17 Actual) uses Pete’s formula to call the correct data from Col AB of the ‘Actual’ sheet.

Col M (Apr-17 Actual), using the same formula, should call the data from Col AC of the ‘Actual’ sheet. But it actually calls the ‘2017 total’ data from Col BB. Same error happens if you select any criteria (Actual. Forecast or Budget) between April – December 2017. Why it happens on those columns, but not the previous ones, is baffling.

Thanks to Jason for clarifying that the formula doesn’t work with Quarterly or Annual data because it doesn’t look for “text”. Hopefully someone out there knows what to add already?

FD and oeldere suggested adding columns and helper cells to simplify things. I’m happy to take any suggestions, but will have to work out where to put them later on. Like most jobs, this is part of a larger file that pulls data from various sources and drops them into defined ranges. There is absolutely no chance the End User will agree to change the entire layout to make this section easier.

oeldere, thanks for the attachment, but would be grateful if you can explain how it work with the two "Headings" the client wants, because I can't see it at the moment?

Interestingly, I have tried the formula on the “real” file, which has over 1k rows, and had no problems with performance on my desktop which is hardly “overclocked”.

So near, yet so far. . . .

Ochimus