Index match multiple arrays

Hi,

I am trying to lookup a column in different sheets and pull through a consolidated list of only unique values. I am using the below formula to do this on one sheet of data, but I have multiple sheets I need to consolidate.

=IFERROR(INDEX(Sheet2!$B$5:$B$12,MATCH(0,INDEX(COUNTIF(B$3:$B4,(Sheet2!$B$5:$B$12)),0,0),0)),"")

I've also attached an example worksheet.

Any help would be greatly appreciated!

Thanks,
Luke

Hi

Use two helper columns D and E.
In D2:D_n make a list of the sheets you want consolidate.
Set E1 equal to the first row for your list (in your example 5). In E2:E_n use and copy down

Formula:

Please Login or Register  to view this content.

Use in B5:B_n the following formula

Formula:

Please Login or Register  to view this content.

See the file for clarification.