Error using Array: Index, Small, Row to pull list from PivotTable

Hi All,

I am currently trying to write a formula to pull back a column of row labels in a pivot table (on another worksheet) based off of the text in a drop-down data validation.

For example, say I have a pivot where the row label in column A is "Species" and there are two types of species, Cat and Dog. Column B of row labels in the pivot is "Breed" and would say Golden Retriever, Labrador, Siamese, etc.

Basically, on the other worksheet if I have the data validation drop-down options as either Cat or Dog, I want to be able to select Dog and then have the formula pull back every Species of Dog from the pivot table in list format.

I am currently using the formula below. The issue somehow lies in pulling from the Pivot Table. When I copy and paste values of a sample of data from the pivot it works perfectly but when I try to reference the same exact data sample within the pivot the formula doesn't work. I've tried messing around with indirect but can't get that to work either. Any ideas?


{=IF(ISERROR(INDEX($E$1:$G$13,SMALL(IF($E$1:$E$13=$C$1,ROW($E$1:$E$13)),ROW(1:1)),3)),"",INDEX($E$1:$G$13,SMALL(IF($E$1:$E$13=$C$1,ROW($E$1:$E$13)),ROW(1:1)),3))}

Thanks in advance for the help!

Thanks for the reply, Tony.

I'm trying to pull back text (row labels) from a pivot table but it could potentially be text or numbers. I'm using excel 2010. The formula is returning blanks which is a result of the iserror "". Seems like there is some sort of change I need to make to the formula so that it can pull from a pivot.

Really appreciate the help.

CatDog.xlsm

Hopefully the attachment works. Let me know if there are issues.

So basically I've got the formula I want working in columns R and S with sample data. When you toggle the drop down between cat and dog it updates the list.

The cells I highlighted yellow (Column T & U) have the same formulas referencing the data in the pivot table which ultimately should pull back the same lists, but I can't get the formula to work with the pivot.

Thanks.

Ah, awesome it worked!

When you get a chance would you mind giving a brief explanation of the logic behind that? Just want to make sure I understand what's going on backing out the min+1.

Thanks again to XOR and Tony for the help!