My brain hurts - Help creating multi-step array/lookup

Greetings,

My problem is reasonably simple to describe, but I haven't found a good way to deal with it other than creating a "helper" sheet, which takes up too much ram, given the amount of data.

I have three sheets, summary, data, and reference.

In data, I have columns: Name and Amount.
In reference, I have: Name and Class.

I want one formula I can use to average the amounts for names in a certain class. Thus, I copy and paste the data in my "data" sheet, and in my "summary" sheet create averages for the listed class.

For example, let's say I paste the following into the data sheet:

Milwaukee	8
Madison	4
Chicago	11
Springfield	16
Des Moines	22

In my "reference" sheet, which stays static, I have:

Milwaukee	Wisconsin
Madison	Wisconsin
Chicago	Illinois
Springfield	Illinois
Des Moines	Iowa

In my "summary" sheet, I have columns for Wisconsin, Illinois, and Iowa, and I want to return the average for each from the data I paste. I want to do this in a single formula, if possible, because I'm dealing with 700,000+ entries and creating a helper sheet makes the document impossible to deal with.

Any thoughts?

Hi,

This looks like an ideal problem for Power Pivot, since you are on 2013.
Try it out.

Regards,

Originally Posted by Speshul

You could add a VLOOKUP column to the data sheet,

=VLOOKUP(City,ReferenceSheet,2,0)

Then just use a SUMIF on those VLOOKUPs with a sum_range of the numbers

No helper sheet, a helper column shouldn't increase size all that much

Thank you, but that's what I'm doing now. With nearly a million VLOOKUP's (for this function alone) it routinely freezes.

Originally Posted by nflsales

Attach sample file

Thanks, here's a quick sample.

Sample.xlsx

Sorry, they shouldn't all be in Wisconsin. I'm not sure why you're seeing that.

No, it doesn't work unfortunately.

Perhaps something like this? Using array formula, pls find the file attach

Hi,

See the attached file, as per my initial suggestion of Power Pivot.