Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Dec 2002
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts
    How do you using Vlookup with multiple worksheets?

    January to December are on different worksheets—my company does not want to combine all the names to just one worksheet for a reason I don’t understand—but whatever.
    The worksheets contain names of expired people with their account #’s. Example if John Smith expired in April he will be under the April worksheet. To find John Smith I need to know that he expired in April and sometimes they do not provide me with this info—So I use the Edit Find in each month very time consuming

    I would like to have just a summery page in which you can type the Person # in B1 and in B2 it will give you that person’s name on matter which worksheet their in. I know how to do this with one worksheet (=VLOOKUP(B1,January!A3:F131,2,FALSE) but how is it done with multiple worksheets?

    Please help I have Excel 2003

  2. #2
    New Lounger
    Join Date
    May 2010
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Brute force option: - do a separate lookup table on your summary page, then search that...

    Code:
    	A	    	B		C	D
    1	Find me:    	=VLOOKUP(B1,Summary!B3:B14,2,FALSE)		
    2				
    3	January		=VLOOKUP(B1,January!A3:F131,2,FALSE)		
    4	February	=VLOOKUP(B1,February!A3:F131,2,FALSE)		
    5	March		=VLOOKUP(B1,March!A3:F131,2,FALSE)		
    6	April		=VLOOKUP(B1,April!A3:F131,2,FALSE)		
    7	May		=VLOOKUP(B1,May!A3:F131,2,FALSE)		
    8	June		=VLOOKUP(B1,June!A3:F131,2,FALSE)		
    9	July		=VLOOKUP(B1,July!A3:F131,2,FALSE)		
    10	August		=VLOOKUP(B1,August!A3:F131,2,FALSE)		
    11	September	=VLOOKUP(B1,September!A3:F131,2,FALSE)		
    12	October		=VLOOKUP(B1,October!A3:F131,2,FALSE)		
    13	November	=VLOOKUP(B1,November!A3:F131,2,FALSE)		
    14	December	=VLOOKUP(B1,December!A3:F131,2,FALSE)

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    199
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Hi,


    This code is from an OZgrid (www.ozgrid.com) newsletter. It may be of use if you can use macros/functions.

    I have not used it myself, but would be most suprised if it did not work.



    VLOOKUP across Worksheets


    This UDF was written by myself to take the place of VLOOKUP when you need

    to look across ALL the Worksheets in the active Workbook. As with the

    Standard VLOOKUP, it stops at the first match.

    ================================================== ==========================

    Function VLOOKAllSheets(Look_Value As Variant, Tble_Array As Range, _

    Col_num as Integer, Optional Range_look as Boolean)



    ''''''''''''''''''''''''''''''''''''''''''''''''

    'Written by OzGrid.com



    'Use VLOOKUP to Look across ALL Worksheets and stops _

    at the first match found.



    ‘=VLOOKAllSheets("Dog",C1:E20,2,FALSE)

    ‘Where "Dog" is the value to find.

    ‘C1:E20 is the range to look in the first column and find "Dog".

    ‘2 is the relative column position in C1:E20 to return our result from.

    ‘FALSE (or omitted) means find an exact match of "Dog".



    '''''''''''''''''''''''''''''''''''''''''''''''''

    Dim wSheet As Worksheet

    Dim vFound



    On Error Resume Next



    For Each wSheet In ActiveWorkbook.Worksheets

    With wSheet

    Set Tble_Array = .Range(Tble_Array.Address)

    vFound = WorksheetFunction.VLookup _

    (Look_Value, Tble_Array, _

    Col_num, Range_look)

    End With

    If Not IsEmpty(vFound) Then Exit For

    Next wSheet



    Set Tble_Array = Nothing

    VLOOKAllSheets = vFound

    End Function

    ================================================== ==========================



    To use this code, do this:



    1. Push Alt+F11 and go to Insert>Module

    2. Copy and paste in the code.

    3. Push Alt+Q and Save.



    Now in any cell put in the Function like this:



    =VLOOKAllSheets("Dog",C1:E20,2,FALSE)



    Where "Dog" is the value to find.

    C1:E20 is the range to look in the first column and find "Dog".

    2 is the relative column position in C1:E20 to return our result from.

    FALSE (or omitted) means find an exact match of "Dog".



    In other words the UDF has the exact same syntax as Excels VLOOKUP. The

    only difference is that it will look in ALL Worksheets and stop at the

    first match. You can find the UDF (VLOOKAllSheets) in the Paste Function

    dialog (Shift+F3) within the "Function category" of "User Defined".

    Good Luck!

    Peter Moran



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •