Return multiple values

I run across this problem many times and know there must be an "easy button" to do this.

I have a spreadsheet where I’m creating a pivot table to sum data, then wish to return more than one field when doing a look up.

An example would be Pivot 1

     A   B   C   D

1   w  10 11 12

2   w  20 21 22

3   w  30 31 32

4   x   40 41 42

5   y   50 51 52

6   x   60 61 62

On Spreadsheet 1, I would like to return all values that match, bringing along all rows and columns.

If looking up “x”, would like to return as below

     A   B   C   D      

1   x   40  41  42

2   x   60  61  62

3  

4  

thanks,

Don 

Don

There is no 'easy button' if that helps. You could however place the field that results in the x,y,z, etc in the pivot table as a 'page field' or report filter, whatever it is called in your version.

From here you can filter the data by just 'x' for example and either copy and paste it or select 'show pages' from the menu. 'show pages' will create a sheet for each option in the page field using the pivot cache.

I am one of the worlds greatest fans of pivot tables but in this case it might be an uphill battle to get back the individual lines. Pivot tables want to return aggregations of the underlying data. That being the case unless you have something to uniquely identify the lines you will get the sum (or some other aggregation) of the values.

One way to return multiple values is to use a query via MSQuery. Take your source data and make it into a named range. Data -> Import External Data -> New Database Query -> Select Excel and then navigate to your workbook. The named ranges will show up. Select the columns you want and then add a criteria. If you put the criteria in square brackets you get a parametric query. With that you can link your parameter to a cell in your spreadsheet and viola... you have the ability to return multiple records.

That is kind of an abbreviated description so if you need more details just ask...

Note that if you move the file the query will no longer work and you will have to remap the query back to your workbook.