2 Questions Involving Lookups for An Array in a different workbook


I am working with multiples trying to automate an end of a month progress.  And a couple questions have arisen that I can't seem to solve.

This formula works to pull the appropriate value
=VLOOKUP(VLOOKUP($A:$A,'Key to 13 Month'!$A:$B,2,FALSE),'Q:\1 - Financial Reporting\2014\08 - 2014 Experiment\PL Prelims\[13 MO Adj EBITDA.xls]Hawaii'!$A$1:$N$65536,14,FALSE)

And this formula works.
{=SUM(VLOOKUP("Storage - Hard Copy",'Q:\1 - Financial Reporting\2014\08 - 2014 Experiment\PL Prelims\[13 MO Adj EBITDA.xls]Hawaii'!$A$1:$N$65536,{7,8,9,10,11,12,13,14},FALSE))}

1)However, I can't seem to turn that second formula into a Vlookup from a hard code.  Is it possible or does it require hard coding?

2)Is there a way to turn the array into a cell reference? {7,8,9,10,11,12,13,14} I've tried with no success.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Richard DanekeTrainerCommented:
Your second formula is an array formula.   This means that an area of your worksheet is performing the calculation.  To set an array formula, one uses Control-Shift-Enter when finished entering the formula in place of the simpler Enter key.
The array in cell reference is to provide the 8 columns that you want to return across the columns where the Vlookup is set.  The array formula can be replaced with a standard vlookup, but the replacement formula will be different for each of the 8 columns where it is in place.
Professor JMicrosoft Excel ExpertCommented:
put in a column like this for example  from A1 to A8 and then instead of

{=SUM(VLOOKUP("Storage - Hard Copy",'Q:\1 - Financial Reporting\2014\08 - 2014 Experiment\PL Prelims\[13 MO Adj EBITDA.xls]Hawaii'!$A$1:$N$65536,{7,8,9,10,11,12,13,14},FALSE))}

put {=SUM(VLOOKUP("Storage - Hard Copy",'Q:\1 - Financial Reporting\2014\08 - 2014 Experiment\PL Prelims\[13 MO Adj EBITDA.xls]Hawaii'!$A$1:$N$65536,A1:A8,FALSE))}

mattfmillerAuthor Commented:
The problem for the second solution, is that I have a different number of columns depending on an input.  

The array works if I have the same number of variables but breaks if I have less or more.  Is there a way can I can reference a particular cell to do that?

Thank you for your help.
Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Professor JMicrosoft Excel ExpertCommented:
in that case, that may need the dynamic range.

are you familiar of creating dynamic expandable range?
Glenn RayExcel VBA DeveloperCommented:
Since you are trying to sum up the values in columns G:N (7-14) where the value in column A is  "Storage - Hard Copy", then you need an array in that third argument to allow this to happen.  And, the formula needs to be an array formula (enter with [Ctrl]+[Shift]+[Enter], curly brackets not shown):

=SUM(VLOOKUP("Storage - Hard Copy",'Q:\1 - Financial Reporting\2014\08 - 2014 Experiment\PL Prelims\[13 MO Adj EBITDA.xls]Hawaii'!$A$1:$N$65536,Column(G:N),FALSE))

If the number/position of columns varies, then you could replace the third argument with a dynamic range name.  But we'll need more information on how that's determined.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mattfmillerAuthor Commented:
I am not familiar with dynamic expandable ranges.

Glenn, I think your column G:N thing might be the solution I'm looking for.  Is there a way to turn that G:N into referencing a cell value?  I'm experimenting with it with no luck so far.
Glenn RayExcel VBA DeveloperCommented:
You could use the INDIRECT function. If you had a cell with the text that specified the columns to look in, for example:
A1: "G:N"  (text value here, no quotes actually used)

you could replace the COLUMN portion in the above formula with

and that would work the same.

So, if there is some trigger that determines the starting column and ending column, you could have that create the value in the "master cell" (A1, in this example) and the formulas would work.

My gut tells me this is not the best solution. :-/    If your criteria for the columns is numeric (say columns 7-10 in one instance, or 7-12 in another) then you could use the ROW function instead - it would be more direct.  

mattfmillerAuthor Commented:
Thank you very much.  That's exactly what I was looking for.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.