Index/Small through Multiple Worksheets

1 post / 0 new
Index/Small through Multiple Worksheets

So I was looking through this website and found a brilliant function to find multiple identical values and list the values next to them.

//fiveminutelessons.com/learn-microsoft-excel/use-index-lookup-mult...

So after reading this, I decided I wanted to create a type of calendar it would pick up the dates in Column I and list them into a calendar.
(Each date has several rows looking up the same value to list the items one by one)
However, I have several of these sheets that I want to to consolidate into a single calendar.
All sheets have dates between I22 and I47 and text between D22:E47.
I was wondering if there was a way I could make this function look-up the values over multiple sheets until it can't find any more.

Below is the function I am currently using. However, it only can look-up values in sheet '354'. I want it to also look things up in sheet '355', '356' and so on.
=IF(ISERROR(INDEX('354'!$D$22:'354'!$I$47,SMALL(IF('354'!$I$22:'354'!$I$47=$A$3,ROW('354'!$I$22:'354'!$I$47)),ROW(1:1))-21,1)),"",INDEX('354'!$D$22:'354'!$I$47,SMALL(IF('354'!$I$22:'354'!$I$47=$A$3,ROW('354'!$I$22:'354'!$I$47)),ROW(1:1))-21,1))

Cell A3 = 4/1/2016
Across several sheets, Cells I22:I47 have multiple dates with 4/1/2016 with different text beside them.
I want the function to look at all of them and list them one by one per cell.

Thank you,
Mutaga

Add new comment