Vlookup over multiple sheets

NL18NAT

New Member
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

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
What issue are you having with that formula?

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?

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.

Replies
1
Views
276
Replies
5
Views
231
Replies
7
Views
158
Replies
0
Views
149
Replies
3
Views
87

Threads
1,214,264
Messages
6,118,563
Members
448,837
Latest member
Josephjet

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back