• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Vlookup function over multiple worksheets

S

Sourin Sett

New Member
I want to use vlookup function over multiple worksheets, i.e. once the search reaches the last row of one of the sheets it will go to the next sheet and continue searching. This I want to continue for 7-8 sheets in a row. Is it possible to complete this without the help of macro? I am not that familiar with macro writing. Please help
 
It is possible with a macro, and also possible with a heavily nested VLOOKUP formula. Here is the formula you would use, formatted for easier viewing:

Code: 
=IFERROR(VLOOKUP(A1,Sheet1!A:A,1,FALSE),
 IFERROR(VLOOKUP(A1,Sheet2!A:A,1,FALSE),
 IFERROR(VLOOKUP(A1,Sheet3!A:A,1,FALSE),
 etc.
 IFERROR(VLOOKUP(A1,Sheet8!A:A,1,FALSE),0))))))))
 
Last edited:
Another approach... but this is array formula:

=VLOOKUP(A1,INDIRECT("'"&INDEX(ListOfWorksheets,MATCH(1,--(COUNTIF(INDIRECT("'"&ListOfWorksheets&"'!$A:$B"),A1)>0),0))&"'!$A:$B"),2,0)

and press CSE

Note: define the name of worksheets in ListOfWorksheets by Name Manager.
 
You must log in or register to reply here.
Back
Top