Vlookup over multiple sheets

[NL18NAT]

I am using an xl file with multiple sheets and have a front sheet at the start where i am cosolidating results. what i am trying to do is a vlook up across the other sheets which will return the stock count of a part number. this is the code I am using

=VLOOKUP(A2,INDIRECT("'"&INDEX(Sheetlist,MATCH(1,--(COUNTIF(INDIRECT("'"&Sheetlist&"'!$A$2:$D$5"),A2)>0),0))&"'!$A$2:$D$5"),3,FALSE)

I need the code to look for the value of Cell A2 across the array of Sheetlist and return the value from cell C2?

Any help appreciated

 

[Domenic]

What issue are you having with that formula?

 

[NL18NAT]

What issue are you having with that formula?

Hi, apologies that would help wouldnt it!!!!
It is just returning an N/A and when i evaluate it is saying there is a circular reference?

 

[Domenic]

That's probably because the list of names in Sheetlist includes the sheet in which your formula resides, and the formula is entered somewhere within A2:D5. By the way, since it looks like you're checking whether A2:A5 on each sheet contains your lookup value, the range referenced for COUNTIF should be $A$2:$A$5, not $A$2:$D$5. So your formula should be...

=VLOOKUP(A2,INDIRECT("'"&INDEX(Sheetlist,MATCH(1,--(COUNTIF(INDIRECT("'"&Sheetlist&"'!$A$2:$A$5"),A2)>0),0))&"'!$A$2:$D$5"),3,FALSE)

...confirmed with CONTROL+SHIFT+ENTER.