Excel triple (multiple) VLOOKUP with a concatenated VLOOKUP value over two worksheets

Question

I want to create a VLOOKUP that is over 2 worksheets and looks at three look up values where one of them is a concatenated with a - in the middle.

Base Data is too big to put in here (30k char limit) so here is the file:

https://wetransfer.com/downloads/b475e4206e9b1c01519b4e6405fbf2f420180208112525/49c87b

In the Transform tab I have tried using the following formula:

=INDEX(Data!D10:BB300,MATCH(A4&"-"&B3,INDEX(Data!D7:AO7,0)),MATCH(Transform!B2,Data!A11:B300,0))

But get #N/A even though I have pointed it to the correct cells.

Expected output is also in the file.

A quick explanation:

In the Data tab I have a row which has dates such as Jan-16, Feb-16, Mar-16 etc

Also in the Data tab I have a row which contains either Actual or Budget

Also in the Data tab I have the value Total Alpha

In the Transform tab I have it laid out like so:

_______________________
|Actual | Total Alpha | 
-----------------------
|       |16     |17   |
-----------------------
|Jan    |       |     |
----------------------- 
|Feb    |       |     |
-----------------------
|Mar    |       |     |
-----------------------
|Apr    |       |     |
-----------------------
|May    |       |     |
-----------------------
|Jun    |       |     |
-----------------------
|Jul    |       |     |
-----------------------
|Aug    |       |     |
-----------------------
|Sep    |       |     |
-----------------------
|Oct    |       |     |
-----------------------
|Nov    |       |     |
-----------------------
|Dec    |       |     |
-----------------------
|       |       |     |
-----------------------
|Budget |       |     |
-----------------------
|       |18     |     |
-----------------------
|Jan    |       |     |
-----------------------
|Feb    |       |     |
-----------------------
|Mar    |       |     |
-----------------------
|Apr    |       |     |
-----------------------
|May    |       |     |
-----------------------
|Jun    |       |     |
-----------------------
|Jul    |       |     |
-----------------------
|Aug    |       |     |
-----------------------
|Sep    |       |     |
-----------------------
|Oct    |       |     |
-----------------------
|Nov    |       |     |
-----------------------
|Dec    |       |     |
 ̅ ̅ ̅ ̅ ̅ ̅ ̅ ̅ ̅ ̅ ̅ ̅ ̅ ̅ ̅ ̅ ̅ ̅ ̅ ̅ ̅ 

And using a triple VLOOKUP I want to be able to look at the (for example): Actual & Total Alpha & Nov & 17 (using something like A4&"-"&B3 to merge the values as the data tab has values like Nov-17) and find the relevant value in the Data tab.

Answer 1

These array formulas should work for your needs.
You must commit them using Ctrl + Shift + Enter.

Transform!B4 should be (copy it through to the remaining cells in the Transform!B4:C15 range):

=INDEX(Data!$A$1:$AL$22,MATCH(Transform!$B$2,Data!$A$1:$A$22,0),MATCH(DATEVALUE($A4&" 1, "&B$3)&$A$2,Data!$A$1:$AL$1&Data!$A$2:$AL$2,0))

Transform!B19 should be (copy it through to the remaining cells in the Transform!B19:B30 range):

=INDEX(Data!$A$1:$AL$22,MATCH(Transform!$B$2,Data!$A$1:$A$22,0),MATCH(DATEVALUE($A19&" 1, "&B$18)&$A$17,Data!$A$1:$AL$1&Data!$A$2:$AL$2,0))

Answer 2

In range B4 put

=INDEX(Data!$A$1:$Z$22,MATCH(Transform!$B$2,Data!$A$1:$A$22,0),MATCH(DATEVALUE(Transform!$A4&Transform!B$3),Data!$1:$1,0))

Drag to fill to C15.

In B19 put

=INDEX(Data!$AA$1:$AL$22,MATCH(Transform!$B$2,Data!$A$1:$A$22,0),MATCH(DATEVALUE(Transform!$A19&Transform!B$18),Data!$1:$1,0)-COLUMN(Data!Z1))

Drag to fill down to B30.

This indexes a lookup range and then finds the row (e.g. total alpha) and column (date) of interest and returns the intersect value.

Change the value in B2 if you want a different line e.g. Total Beta

Version 2: Dynamic lookup of Actual and Target - can be tidied up somewhat

Create two named ranges

Ctrl + F3 to open name manager and then add

Name: ActualRange

RefersTo:

=OFFSET(Data!$A$1,0,0,ROWS(Data!$1:$22),MATCH(Transform!$A$17,Data!$2$2,0)-1)

Name: BudgetRange

RefersTo:

=OFFSET(Data!$A$1,0,MATCH(Transform!$A$17,Data!$2$2,0)-1, ROWS(Data!$1:$22),MATCH(Transform!$A$17,Data!$2$2,0))

Then in B4 dragged to fill to C15 goes:

=INDEX(ActualRange,MATCH(Transform!$B$2,Data!$A$1:$A$22,0),MATCH(DATEVALUE(Transform!$A4&Transform!B$3),Data!$1:$1,0)

Then in B19 dragged to fill to B30 goes:

=INDEX(BudgetRange,MATCH(Transform!$B$2,Data!$A$1:$A$22,0),MATCH(DATEVALUE(Transform!$A19&Transform!B$18),Data!$1:$1,0)+ROWS($A$19:$A19))

There is some scope to further reduce the indexed ranges which are currently set at entire rows.