VLOOKUP with Dynamic Column Index Based on 2 Variables

[Cowzilla]

Hi All,

Here's my problem: (Excel 2003) I have two files, one of which is a data and the other is a summary file. The data file has headers in 2 different rows with data entered below. Then there are summary and total rows for that data.
The summary file pulls from the data file, and I need a VLOOKUP to automate that process.
I need the formula to match the headers in the first two rows of the data file with the columns in the summary file and return the appropriate value.
The columns are NOT static - they are re-arranged frequently.

Here are some exmples:

DATA FILE:

Row 1 Cheese Regular Cheese Regular
Row 2 Burger Burger Hotdog Hotdog
5 6 7 2
____________________________
Eaten 1 3 3 1
Not Eaten 4 3 4 1
Total 5 6 7 2

SUMMARY FILE:
Column 1 Column 2 Eaten Total
Cheese Burger 1 5 NEED FORULA HERE
Regular Hotdog 1 2

Any help would be greatly appreciated!

 

[danzon]

Can you insert a column at very top of your data file...

If you can insert a column then you can use in A1 for example

=B1&" "&C1 to create Cheese Burger in A1

Then you can use Vlookup with Match to do the vlookup and return the correct column

 

[Cowzilla]

Thank you for the reply. It's a good idea, but not possible. This is an old company format and the only changes I can make are to the formulas in the summary file.

 

[danzon]

Then you will need to use SUMPRODUCT...

maybe like this

You will need to adjust to reference the other sheet but something like this.. ...

=SUMPRODUCT(--(C1:AA1=A12),--(C2:AA2=B12),(C3:AA3))

Here is a screenshot to help explain what is going on here

 

[Cowzilla]

Is there any way to make the last array reference the "Eaten" row, rather than a named range of cells? The locations are constantly changing, and it doesn't seem to be pulling in the correct row.
It will pull in the "Eaten" value sometimes and the "Total" value others.