+ Reply to Thread
Results 1 to 7 of 7

vlookup multiple worksheets

  1. #1
    Laura
    Guest

    vlookup multiple worksheets

    I'm in over my head. Any help would be appreciated.

    I have a workbook with 11 worksheets. I need to look for a value in column
    A within the first 10 worksheets and return the corresponding value from
    column F to worksheet number 11. Column A contains both text and numbers.

    Column A Column F
    Product Units
    5 200
    1 100
    3 12
    7 50

    The numbers in column A are indexes from drop down boxes.

    Please let me know if you need further info. Thank you for any help you can
    give.

    Laura

  2. #2
    Domenic
    Guest

    Re: vlookup multiple worksheets

    Assuming that B1 on Sheet11 contains your lookup value, and Sheet1
    through Sheet10 contain your lookup tables, try...

    =VLOOKUP(B1,INDIRECT("Sheet"&MATCH(TRUE,COUNTIF(INDIRECT("Sheet"&ROW(INDI
    RECT("1:10"))&"!A2:A100"),B1)>0,0)&"!A2:F100"),6,0)

    or

    =VLOOKUP(B1,INDIRECT("'"&INDEX($A$1:$A$10,MATCH(TRUE,COUNTIF(INDIRECT("'"
    &$A$1:$A$10&"'!A2:A100"),B1)>0,0))&"'!A2:F100"),6,0)

    ....where A1:A10 on Sheet11 contains your list of sheet names. Both
    formulas need to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER.
    Adjust the ranges (A2:A100 and A2:F100) accordingly.

    Hope this helps!

    In article <[email protected]>,
    "Laura" <[email protected]> wrote:

    > I'm in over my head. Any help would be appreciated.
    >
    > I have a workbook with 11 worksheets. I need to look for a value in column
    > A within the first 10 worksheets and return the corresponding value from
    > column F to worksheet number 11. Column A contains both text and numbers.
    >
    > Column A Column F
    > Product Units
    > 5 200
    > 1 100
    > 3 12
    > 7 50
    >
    > The numbers in column A are indexes from drop down boxes.
    >
    > Please let me know if you need further info. Thank you for any help you can
    > give.
    >
    > Laura


  3. #3
    Laura
    Guest

    Re: vlookup multiple worksheets

    Domenic:

    Thanks for your reply. As I mentioned, this is over my head. My function
    experience has been limited to IF statements. In the first formula, is
    "Sheet" a range name? My lookup value is the index number 1 which could be
    on any sheet 1-10 and the tables are in sheets 1 - 10. I tried the first
    formula and received #N/A. I will try the second formula and check back with
    you.

    Thanks again,
    Laura

    "Domenic" wrote:

    > Assuming that B1 on Sheet11 contains your lookup value, and Sheet1
    > through Sheet10 contain your lookup tables, try...
    >
    > =VLOOKUP(B1,INDIRECT("Sheet"&MATCH(TRUE,COUNTIF(INDIRECT("Sheet"&ROW(INDI
    > RECT("1:10"))&"!A2:A100"),B1)>0,0)&"!A2:F100"),6,0)
    >
    > or
    >
    > =VLOOKUP(B1,INDIRECT("'"&INDEX($A$1:$A$10,MATCH(TRUE,COUNTIF(INDIRECT("'"
    > &$A$1:$A$10&"'!A2:A100"),B1)>0,0))&"'!A2:F100"),6,0)
    >
    > ....where A1:A10 on Sheet11 contains your list of sheet names. Both
    > formulas need to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER.
    > Adjust the ranges (A2:A100 and A2:F100) accordingly.
    >
    > Hope this helps!
    >
    > In article <[email protected]>,
    > "Laura" <[email protected]> wrote:
    >
    > > I'm in over my head. Any help would be appreciated.
    > >
    > > I have a workbook with 11 worksheets. I need to look for a value in column
    > > A within the first 10 worksheets and return the corresponding value from
    > > column F to worksheet number 11. Column A contains both text and numbers.
    > >
    > > Column A Column F
    > > Product Units
    > > 5 200
    > > 1 100
    > > 3 12
    > > 7 50
    > >
    > > The numbers in column A are indexes from drop down boxes.
    > >
    > > Please let me know if you need further info. Thank you for any help you can
    > > give.
    > >
    > > Laura

    >


  4. #4
    Domenic
    Guest

    Re: vlookup multiple worksheets

    In article <[email protected]>,
    "Laura" <[email protected]> wrote:

    > Domenic:
    >
    > Thanks for your reply.


    You're very welcome!

    > In the first formula, is "Sheet" a range name?


    No, it's used to reference your 10 worksheets.

    > My lookup value is the index number 1 which could be
    > on any sheet 1-10 and the tables are in sheets 1 - 10.


    Can you confirm the range for your lookup table? Also, are they all
    located in the same columns for each worksheet?

    > I tried the first
    > formula and received #N/A. I will try the second formula and check back with
    > you.


    If your sheets 1 - 10 are named Sheet1, Sheet2, Sheet3, through Sheet10,
    then the first formula would suffice.

  5. #5
    Laura
    Guest

    Re: vlookup multiple worksheets

    YES!!!!! The first formula worked! Someday I'll understand what happened.

    Thanks again,
    Laura

    "Domenic" wrote:

    > In article <[email protected]>,
    > "Laura" <[email protected]> wrote:
    >
    > > Domenic:
    > >
    > > Thanks for your reply.

    >
    > You're very welcome!
    >
    > > In the first formula, is "Sheet" a range name?

    >
    > No, it's used to reference your 10 worksheets.
    >
    > > My lookup value is the index number 1 which could be
    > > on any sheet 1-10 and the tables are in sheets 1 - 10.

    >
    > Can you confirm the range for your lookup table? Also, are they all
    > located in the same columns for each worksheet?
    >
    > > I tried the first
    > > formula and received #N/A. I will try the second formula and check back with
    > > you.

    >
    > If your sheets 1 - 10 are named Sheet1, Sheet2, Sheet3, through Sheet10,
    > then the first formula would suffice.
    >


  6. #6
    Laura
    Guest

    Re: vlookup multiple worksheets

    Domenic:

    Sorry to bother you again, but I have one more question. How do I keep the
    formula from returning #N/A when the value is not found?

    Thanks,
    Laura

    "Domenic" wrote:

    > In article <[email protected]>,
    > "Laura" <[email protected]> wrote:
    >
    > > Domenic:
    > >
    > > Thanks for your reply.

    >
    > You're very welcome!
    >
    > > In the first formula, is "Sheet" a range name?

    >
    > No, it's used to reference your 10 worksheets.
    >
    > > My lookup value is the index number 1 which could be
    > > on any sheet 1-10 and the tables are in sheets 1 - 10.

    >
    > Can you confirm the range for your lookup table? Also, are they all
    > located in the same columns for each worksheet?
    >
    > > I tried the first
    > > formula and received #N/A. I will try the second formula and check back with
    > > you.

    >
    > If your sheets 1 - 10 are named Sheet1, Sheet2, Sheet3, through Sheet10,
    > then the first formula would suffice.
    >


  7. #7
    Domenic
    Guest

    Re: vlookup multiple worksheets

    In article <[email protected]>,
    "Laura" <[email protected]> wrote:

    > Domenic:
    >
    > Sorry to bother you again, but I have one more question.


    No problem...

    >How do I keep the formula from returning #N/A when the value is not

    found?

    One option...

    Enter the following formula in a cell, let's say C1:

    =SUMPRODUCT(COUNTIF(INDIRECT("Sheet"&ROW(INDIRECT("1:10"))&"!A2:A100"),B1
    ))

    ....where B1 contains your lookup value.

    Enter the following formula in another cell, let's say D1:

    =IF(N(C1),VLOOKUP(B1,INDIRECT("Sheet"&MATCH(TRUE,COUNTIF(INDIRECT("Sheet"
    &ROW(INDIRECT("1:10"))&"!A2:A100"),B1)>0,0)&"!A2:F100"),6,0),"")

    ....confirmed with CONTROL+SHIFT+ENTER.

    Another option...

    Use conditional formatting to hide the errors. Assuming that the
    formula is entered in D1:

    1) Select D1

    2) Format > Conditional Formatting > Formula Is

    3) Enter the following formula:

    =ISNA(D1)

    4) Choose 'White' as your font colour

    5) Click Ok

    Hope this helps!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1