lookup function to display multiple results from multiple sheets

task sovled

With Excel 365, you should look at using FILTER. For example:

Formula:

=FILTER('Test case 1'!G13:G22,'Test case 1'!F13:F22="Failed")

Please updater your profile, it's indicated you use O365 of excel not Excel 2016.

If you don't mind to create listing of sheetname manually and a lot of helper cells.

1. Create table contains sheetname and use countif + indirect to count for 'failed' in each sheet.

2. Use helper cells.

Sheetname : K2
=IF(ISTEXT(M1),INDEX(Table1[[#All],[Sheetname]],MATCH(K1,Table1[[#All],[Sheetname]],0)+1),
IF(M1<L1,K1,
IF(K1<>0,INDEX(Table1[[#All],[Sheetname]],MATCH(K1,Table1[[#All],[Sheetname]],0)+1),0)))

Total of failed : L2
=IF(K2<>0,VLOOKUP(K2,Table1[#All],2,FALSE),0)

Current Row : M2
=IF(K2<>0,IF(K1<>K2,IF(L2>0,1,0),M1+1),0)

3. Output area
A2
=IF($M2=1,INDIRECT("'"&$K2&"'!G3"),"")

B2
=IF($M2=1,INDIRECT("'"&$K2&"'!D2"),"")

C2 (note : the formula is fixed to rows 13-22)
=IF($M2>0,INDEX(INDIRECT("'"&$K2&"'!G13:G22"),AGGREGATE(15,6,(ROW(INDIRECT("'"&$K2&"'!F13:F22"))-12)/(1/((INDIRECT("'"&$K2&"'!F13:F22"))="failed")),$M2)),"")

D2
=IF($M2=1,INDIRECT("'"&$K2&"'!G5"),"")

E2
=IF($M2=1,INDIRECT("'"&$K2&"'!G7"),"")

Regards.

@Menem

Thank you very much, the solution works perfectly.

Appreciate your help.

i discovered the filter function in 365, unfortunately i am using Excel 2016

Then please update your misleading forum profile. Thanks.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.