Index Match VBA Multiple Worksheets Many Rows

Hi All,

I have a workbook that has 5 worksheets. Worksheet1 is the Summary tab, Worksheets 2-5 contains data all sitting under the same headings and formats however vary in row number e.g.

Worksheet 2 - 63000 rows
Worksheet 3 - 48000 rows
Worksheet 4 - 23000 rows
Worksheet 5 - 21000 rows

In Worksheet 1 Column AQ contains a Cost Centre number which I would like to extract the parent description of in Column BI of the same Worksheet and then the child description into Column BJ. Worksheet 1 currently has 16000+ rows and grows daily.

The location of the Cost Centre in Worksheets 2-4 is contained in Column A and the Parent Description in Column W and the Child Description in Column Y.

I am currently using this INDEX MATCH formula to search all worksheets but as you can imagine it's tediously slow and needless to say sometimes not reporting/updating the values correctly likely because it's frozen.

=VLOOKUP(AQ2,INDIRECT("'"&INDEX(Sheet1!$A$1:$A$4,MATCH(TRUE,
COUNTIF(INDIRECT("'"&Sheet1!$A$1:$A$4&"'!A2:A63355"),AQ2)>0,0))&"'!A2:W63355"),23,0) this looks up Cost Centre in Worksheet 1 Column AQ and returns the results from Column W in Worksheets 2-4 to Worksheet 1 Column BI

=VLOOKUP(AQ2,INDIRECT("'"&INDEX(Sheet1!$A$1:$A$4,MATCH(TRUE,
COUNTIF(INDIRECT("'"&Sheet1!$A$1:$A$4&"'!A2:A63355"),AQ2)>0,0))&"'!A2:Y63355"),25) this looks up Cost Centre in Worksheet 1 Column AQ and returns the results from Column Y in Worksheets 2-4 to Worksheet 1 Column BJ


Sheet1!$A$1:$A$4 simply contains a lookup table containing Worksheets 2-4 names.

Can anyone help in either optimising this formula or would a VBA solution be more efficient. If VBA is the way forward then could I ask that it be written so that should I need to add a column to report back on then it is easy enough to do so simply by inputting the column identifier within the array.

I have Excel 2007.

Looking forward to all responses.

Works a charm

Any chance it can written so that it does not knock the column formatting out i.e. Remove the Cell borders and shading?

Ok think it was my error.

Thank you so much for your help