Vlookup over multiple sheets

NL18NAT

New Member
Joined
Sep 19, 2018
Messages
3
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

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
20,212
Office Version
  1. 365
Platform
  1. Windows
What issue are you having with that formula?
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
20,212
Office Version
  1. 365
Platform
  1. Windows
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,487
Messages
5,831,981
Members
430,100
Latest member
namhnz

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

Which adblocker are you using?

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
Top