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
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,299
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,911
Messages
5,525,591
Members
409,652
Latest member
strangelyangely

This Week's Hot Topics

Top