Vlookup across worksheets, same workbook providing a sum of returned values

**IST** · 12-17-2010, 03:53 PM

I am looking for a vlookup formula where the the table array is the entire workbook, not just a range of cells within a particular worksheet. I also want the vlookup to return a sum of all returned values, not just the first instance.

ie... I ordered ceiling tile #2 on 10/30/10 (cost me $100), 11/4/10 ($100) and 11/12/10 ($200) and I want the vlookup to search the workbook, find every instance that I order ceiling tile #2 and return a value of $400, not just $100.

Is this possible? Thanks in advace.

PS- I'm tryining to create this vlookup in the same workbook, not link to another. I'm using Excel 2007.

**NBVC** · 12-17-2010, 03:54 PM

How are the sheets named?

**NBVC** · 12-17-2010, 04:04 PM

Your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us help you!

Read this to understand why we ask you to do this

**Richard Buttrey** · 12-17-2010, 04:08 PM

Hi,

First things first. Even if there were such a thing as a standard 3D VLOOKUP it would only return a single value. That's the essence of any VLOOKUP(), MATCH() or INDEX() function. What you want is the sum of several cells.

If there is a common structure to your data with product #s in the same column on each sheet and there was only ever one instance of ceiling tile #2 on each sheet then you could use a combination of VLOOKUP() and INDIRECT() functions on a summary sheet which contained the names of all your sheets in a list.
You could think of this as a pseudo 3D VLOOKUP. Take a look at

https://sites.google.com/a/madrocket...ions/3d-lookup

or just google Excel 3d VLOOKUP for other ideas.

After that you're other choice is a VBA solution which interrogates all your sheets and builds its own total.

HTH