Results 1 to 5 of 5

Thread: Returning a value using multiple criteria

  1. #1
    New Member
    Join Date
    Jul 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Returning a value using multiple criteria

    Hi All

    I need help with the below table if you can please:

    I am trying to return a value listed in the table by entering the following:

    Eaves Height - 3-6
    Cladding Type - Single Skin or Composite
    Gable Width - 3-25

    Eaves m> 3 3 4 4 5 5 6 6
    Gable m SingleSkin Composite SingleSkin Composite SingleSkin Composite SingleSkin Composite
    3 1100 1300 1200 1400 1300 1500 1400 1600
    4 1100 1300 1200 1400 1300 1500 1400 1600
    5 1100 1300 1200 1400 1300 1500 1400 1600
    6 1100 1300 1200 1400 1300 1500 1400 1600
    7 1100 1300 1200 1400 1300 1500 1400 1600
    8 1100 1300 1200 1400 1300 1500 1400 1600
    9 1100 1300 1200 1400 1300 1500 1400 1600
    10 1100 1300 1200 1400 1300 1500 1400 1600
    11 1300 1500 1400 1600 1500 1700 1600 1800
    12 1300 1500 1400 1600 1500 1700 1600 1800
    13 1300 1500 1400 1600 1500 1700 1600 1800
    14 1300 1500 1400 1600 1500 1700 1600 1800
    15 1300 1500 1400 1600 1500 1700 1600 1800
    16 1500 1700 1600 1800 1700 1900 1600 2000
    17 1500 1700 1600 1800 1700 1900 1600 2000
    18 1500 1700 1600 1800 1700 1900 1600 2000
    19 1500 1700 1600 1800 1700 1900 1600 2000
    20 1500 1700 1600 1800 1700 1900 1600 2000
    21 1700 1900 1800 2000 1900 2100 1800 2200
    22 1700 1900 1800 2000 1900 2100 1800 2200
    23 1700 1900 1800 2000 1900 2100 1800 2200
    24 1700 1900 1800 2000 1900 2100 1800 2200
    25 1700 1900 1800 2000 1900 2100 1800 2200
    Eaves 6
    Span 10
    Cladding Composite
    Cost Per Bay #VALUE!

    Any help would be gratefully received

    Thank you

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

    Default Re: Returning a value using multiple criteria

    Hi & welcome to MrExcel
    How about

    ABCDEFGHI
    1Eaves m>33445566
    2Gable mSingleSkinCompositeSingleSkinCompositeSingleSkinCompositeSingleSkinComposite
    3311001300120014001300150014001600
    4411001300120014001300150014001600
    5511001300120014001300150014001600
    6611001300120014001300150014001600
    7711001300120014001300150014001600
    8811001300120014001300150014001600
    9911001300120014001300150014001600
    101011001300120014001300150014001600
    111113001500140016001500170016001800
    121213001500140016001500170016001800
    131313001500140016001500170016001800
    141413001500140016001500170016001800
    151513001500140016001500170016001800
    161615001700160018001700190016002000
    171715001700160018001700190016002000
    181815001700160018001700190016002000
    191915001700160018001700190016002000
    202015001700160018001700190016002000
    212117001900180020001900210018002200
    222217001900180020001900210018002200
    232317001900180020001900210018002200
    242417001900180020001900210018002200
    252517001900180020001900210018002200
    26
    27Eaves3
    28Span25
    29CladdingComposite
    30
    31
    32
    33
    34Cost Per Bay1900

    Summary



    Worksheet Formulas
    CellFormula
    B34=SUMPRODUCT((A3:A25=B28)*(B1:I1=B27)*(B2:I2=B29),B3:I25)

    - 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 steve the fish's Avatar
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,669
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Returning a value using multiple criteria

    Hi. There will be a few ways to do this. Heres one:

    =INDEX($B$3:$I$25,MATCH(B28,$A$3:$A$25,0),MATCH(1,INDEX(($B$1:$I$1=B27)*($B$2:$I$2=B29),0),0))

  4. #4
    New Member
    Join Date
    Jul 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Returning a value using multiple criteria

    Thank you Fluff, that is fantastic.

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

    Default Re: Returning a value using multiple criteria

    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

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
  •