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

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.
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.
 
Upvote 0

Forum statistics

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

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
Back
Top