INDEX SMALL for multiple results from multiple worksheets

[JPP]

Hi, I am working on a spreadsheet that has several worksheets and another worksheet which is a summary of the others. Each worksheet has the same template.

In the summary worksheet, I am trying to pull all the relevant data from all the worksheets. See attached.

When I select a colour, I want to INDEX the Vegetables and Fruit worksheets in the array, so when Red is selected, 'Pepper', 'Radish' and 'Apple' appear in the results. I have got it working for the Vegetables worksheet, but can't figure out how the formula can be changed to include another worksheet in the INDEX.

Please can someone help?

Thanks.

 

[rahulshewale1]

Hii JPP,

How many worksheet has in workbook?
Which excel version do you use ?

You can achieve desire result through power query .free add in download available above excel 2010 .


Regard
Rahul shewale

 

[JPP]

I have four worksheets including the summary, using Excel 2016. I'd rather stay away from Add-ins and prefer to do this using a formula. Thanks.

 

[bosco_yip]

Try,

1] In A3, array formula (entered with CSE) copied down :

=IFERROR(IF(ROW(A1)<=COUNTIF(Vegetables!B$3:B$30,A$2),INDEX(Vegetables!A$3:A$30,SMALL(IF(Vegetables!B$3:B$30=A$2,ROW(Vegetables!$A$1:$A$28)),ROW(A1))),INDEX(Fruit!A$3:A$30,SMALL(IF(Fruit!B$3:B$30=A$2,ROW(Fruit!$A$1:$A$28)),IF(ROW(A1)<=COUNTIF(Vegetables!B$3:B$30,A$2),ROW(A1),COUNTIF(Vegetables!B$3:B$30,A$2)+COUNTIF(Fruit!B$3:B$30,A$2)+1-ROW(A1))))),"")

2] In B3, copied down :

=IF(A3="","",A$2)

Regards
Bosco

 

[rahulshewale1]

hii @JPP

if you are using excel 2016 then do not need to download add-in ,it's is bulit in power query,

Power Query is fast ,


Regard
Rahul shewale

 

[JPP]

Thanks Bosco. Changed the formula to match my table - although some time to get my head around it, and it now works a treat. Although, I have pasted it into another column (with alterations) and am getting "0" where there is no value in the corresponding cell.