Using VLOOKUP to apply an ARRAY formula

**monkdelafunk** · 08-28-2008, 02:21 PM

Hi all,

I have this array formula which I will be using to sum an overall total:

Please Login or Register  to view this content.

What I would also like to do is run a VLOOKUP formula in another sheet (lets call it sheet2) where values in column A (name) are the reference for the VLOOKUP. I then just want to return a result for the row starting with that name in sheet1.

Frankly VLOOKUP confuses me a little, and without the help of the mighty Ron the above formula wouldn't even exist!

Any help greatfully received!

**Gratis** · 08-29-2008, 10:22 PM

Having a hard time understanding how your question related to that 'sum' you've described...

Are you looking for that same 'sum' in a table that contains various possible 'sums' in column A? I'm assuming there's a column 'B' that contains some values you want to return once you've found that item in column 'A'.

What's throwing me off here though is your use of the word 'name' in the last line of your message. But I'm going to assume you mean 'sum' otherwise you're going to have to clarify - or post the sheet.

Anyhow, if this is the case (ie. looking for a 'sum'), it's a simple enough task. Let's assume you're storing what you want to find in cell "BZ1" (based on your sum formula, I'm just guessing here). Let's say you have 200 possible values of this listed in column A of sheet2. Just pick a cell say "G1" in sheet2 and type:
=VLOOKUP(sheet1!BZ1,A1:B200,2)
If excel finds that sum in your table, it'll then return in G1 the value of the contents of column B (column "2" of that array) right next door to that value.

There are some other optional parameters for VLOOKUP, for example if you are looking for an exact match vs. just 'something close'.

Let me know if this helps. Feel free to contact directly - [email protected]

Dr. Elliot Bendoly
Emory University
www.bizbreed.com