Results 1 to 8 of 8

Thread: Picking Values from a Table
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jan 2006
    Location
    Townsville, Queensland
    Posts
    234
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Picking Values from a Table

    Hi All

    I am inputting values into row 2 under certain labels. Each label has a value which corresponds with the values in table A5:B9. The total is then shown in cell B12 (product of a multiplication).

    I am trying to remove the intermediate step which requires table D12:E16. I assume a MATCH / INDEX / HLOOKUP or SUMPRODUCT formula may be the answer but I'm not sure.

    There are only 5 labels shown here but I need the flexibility to expand.

    Thanks




    ABCDE
    1ABCDEFGHIJKLMNO
    220506
    3
    4
    5ABC16.1
    6DEF12.8
    7GHI15.4
    8JKL11.4
    9MNO12.5
    10
    11
    12Total184.2ABC32.2
    13DEF0.0
    14GHI77.0
    15JKL0.0
    16MNO75.0

    Sheet2



    Worksheet Formulas
    CellFormula
    B12=SUM(E12:E16)
    E12=A2*B5
    E13=B2*B6
    E14=C2*B7
    E15=D2*B8
    E16=E2*B9


  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,229
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Picking Values from a Table

    How about
    =SUMPRODUCT((A1:E1=A5:A9)*(A2:E2)*(B5:B9))
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    Board Regular
    Join Date
    Jan 2006
    Location
    Townsville, Queensland
    Posts
    234
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Picking Values from a Table

    Hey thanks very much Fluff. Works a breeze.

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,229
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Picking Values from a Table

    You're welcome & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    Board Regular
    Join Date
    Jan 2006
    Location
    Townsville, Queensland
    Posts
    234
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Picking Values from a Table

    Hi Fluff

    I've found one small issue. If the values in B5:B9 are arrived at using a formula, I get the #Value ! error.

    Is there a way around this so that I can use the values that are from a formula ?

    Thanks

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,229
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Picking Values from a Table

    The fact that you have formulae in B5:B9 shouldn't make any difference, unless one or more of them is returning #VALUE ! or "", in which case you need to change that formula so that it returns 0
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  7. #7
    Board Regular
    Join Date
    Jan 2006
    Location
    Townsville, Queensland
    Posts
    234
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Picking Values from a Table

    Correct again Fluff. I did have an IF statement that returned a "" if true. I have now changed this to return 0.

    All works fine again thanks.

  8. #8
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,229
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Picking Values from a Table

    Glad to help & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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