Returning a value using multiple criteria

RobNSB

New Member
Joined
Jul 8, 2019
Messages
4
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>33445566
Gable mSingleSkinCompositeSingleSkinCompositeSingleSkinCompositeSingleSkinComposite
311001300120014001300150014001600
411001300120014001300150014001600
511001300120014001300150014001600
611001300120014001300150014001600
711001300120014001300150014001600
811001300120014001300150014001600
911001300120014001300150014001600
1011001300120014001300150014001600
1113001500140016001500170016001800
1213001500140016001500170016001800
1313001500140016001500170016001800
1413001500140016001500170016001800
1513001500140016001500170016001800
1615001700160018001700190016002000
1715001700160018001700190016002000
1815001700160018001700190016002000
1915001700160018001700190016002000
2015001700160018001700190016002000
2117001900180020001900210018002200
2217001900180020001900210018002200
2317001900180020001900210018002200
2417001900180020001900210018002200
2517001900180020001900210018002200
Eaves6
Span10
CladdingComposite
Cost Per Bay#VALUE!

<colgroup><col width="104" span="9" style="width:78pt"> </colgroup><tbody>
</tbody>

<tbody>
</tbody>

Any help would be gratefully received

Thank you
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
44,731
Office Version
365
Platform
Windows
Hi & welcome to MrExcel
How about

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Eaves m></td><td style="text-align: right;;">3</td><td style="text-align: right;;">3</td><td style="text-align: right;;">4</td><td style="text-align: right;;">4</td><td style="text-align: right;;">5</td><td style="text-align: right;;">5</td><td style="text-align: right;;">6</td><td style="text-align: right;;">6</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Gable m</td><td style=";">SingleSkin</td><td style=";">Composite</td><td style=";">SingleSkin</td><td style=";">Composite</td><td style=";">SingleSkin</td><td style=";">Composite</td><td style=";">SingleSkin</td><td style=";">Composite</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">3</td><td style="text-align: right;;">1100</td><td style="text-align: right;;">1300</td><td style="text-align: right;;">1200</td><td style="text-align: right;;">1400</td><td style="text-align: right;;">1300</td><td style="text-align: right;;">1500</td><td style="text-align: right;;">1400</td><td style="text-align: right;;">1600</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">4</td><td style="text-align: right;;">1100</td><td style="text-align: right;;">1300</td><td style="text-align: right;;">1200</td><td style="text-align: right;;">1400</td><td style="text-align: right;;">1300</td><td style="text-align: right;;">1500</td><td style="text-align: right;;">1400</td><td style="text-align: right;;">1600</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">5</td><td style="text-align: right;;">1100</td><td style="text-align: right;;">1300</td><td style="text-align: right;;">1200</td><td style="text-align: right;;">1400</td><td style="text-align: right;;">1300</td><td style="text-align: right;;">1500</td><td style="text-align: right;;">1400</td><td style="text-align: right;;">1600</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;">6</td><td style="text-align: right;;">1100</td><td style="text-align: right;;">1300</td><td style="text-align: right;;">1200</td><td style="text-align: right;;">1400</td><td style="text-align: right;;">1300</td><td style="text-align: right;;">1500</td><td style="text-align: right;;">1400</td><td style="text-align: right;;">1600</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;">7</td><td style="text-align: right;;">1100</td><td style="text-align: right;;">1300</td><td style="text-align: right;;">1200</td><td style="text-align: right;;">1400</td><td style="text-align: right;;">1300</td><td style="text-align: right;;">1500</td><td style="text-align: right;;">1400</td><td style="text-align: right;;">1600</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;">8</td><td style="text-align: right;;">1100</td><td style="text-align: right;;">1300</td><td style="text-align: right;;">1200</td><td style="text-align: right;;">1400</td><td style="text-align: right;;">1300</td><td style="text-align: right;;">1500</td><td style="text-align: right;;">1400</td><td style="text-align: right;;">1600</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;">9</td><td style="text-align: right;;">1100</td><td style="text-align: right;;">1300</td><td style="text-align: right;;">1200</td><td style="text-align: right;;">1400</td><td style="text-align: right;;">1300</td><td style="text-align: right;;">1500</td><td style="text-align: right;;">1400</td><td style="text-align: right;;">1600</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;">10</td><td style="text-align: right;;">1100</td><td style="text-align: right;;">1300</td><td style="text-align: right;;">1200</td><td style="text-align: right;;">1400</td><td style="text-align: right;;">1300</td><td style="text-align: right;;">1500</td><td style="text-align: right;;">1400</td><td style="text-align: right;;">1600</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;;">11</td><td style="text-align: right;;">1300</td><td style="text-align: right;;">1500</td><td style="text-align: right;;">1400</td><td style="text-align: right;;">1600</td><td style="text-align: right;;">1500</td><td style="text-align: right;;">1700</td><td style="text-align: right;;">1600</td><td style="text-align: right;;">1800</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="text-align: right;;">12</td><td style="text-align: right;;">1300</td><td style="text-align: right;;">1500</td><td style="text-align: right;;">1400</td><td style="text-align: right;;">1600</td><td style="text-align: right;;">1500</td><td style="text-align: right;;">1700</td><td style="text-align: right;;">1600</td><td style="text-align: right;;">1800</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="text-align: right;;">13</td><td style="text-align: right;;">1300</td><td style="text-align: right;;">1500</td><td style="text-align: right;;">1400</td><td style="text-align: right;;">1600</td><td style="text-align: right;;">1500</td><td style="text-align: right;;">1700</td><td style="text-align: right;;">1600</td><td style="text-align: right;;">1800</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style="text-align: right;;">14</td><td style="text-align: right;;">1300</td><td style="text-align: right;;">1500</td><td style="text-align: right;;">1400</td><td style="text-align: right;;">1600</td><td style="text-align: right;;">1500</td><td style="text-align: right;;">1700</td><td style="text-align: right;;">1600</td><td style="text-align: right;;">1800</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style="text-align: right;;">15</td><td style="text-align: right;;">1300</td><td style="text-align: right;;">1500</td><td style="text-align: right;;">1400</td><td style="text-align: right;;">1600</td><td style="text-align: right;;">1500</td><td style="text-align: right;;">1700</td><td style="text-align: right;;">1600</td><td style="text-align: right;;">1800</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style="text-align: right;;">16</td><td style="text-align: right;;">1500</td><td style="text-align: right;;">1700</td><td style="text-align: right;;">1600</td><td style="text-align: right;;">1800</td><td style="text-align: right;;">1700</td><td style="text-align: right;;">1900</td><td style="text-align: right;;">1600</td><td style="text-align: right;;">2000</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style="text-align: right;;">17</td><td style="text-align: right;;">1500</td><td style="text-align: right;;">1700</td><td style="text-align: right;;">1600</td><td style="text-align: right;;">1800</td><td style="text-align: right;;">1700</td><td style="text-align: right;;">1900</td><td style="text-align: right;;">1600</td><td style="text-align: right;;">2000</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">18</td><td style="text-align: right;;">18</td><td style="text-align: right;;">1500</td><td style="text-align: right;;">1700</td><td style="text-align: right;;">1600</td><td style="text-align: right;;">1800</td><td style="text-align: right;;">1700</td><td style="text-align: right;;">1900</td><td style="text-align: right;;">1600</td><td style="text-align: right;;">2000</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">19</td><td style="text-align: right;;">19</td><td style="text-align: right;;">1500</td><td style="text-align: right;;">1700</td><td style="text-align: right;;">1600</td><td style="text-align: right;;">1800</td><td style="text-align: right;;">1700</td><td style="text-align: right;;">1900</td><td style="text-align: right;;">1600</td><td style="text-align: right;;">2000</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">20</td><td style="text-align: right;;">20</td><td style="text-align: right;;">1500</td><td style="text-align: right;;">1700</td><td style="text-align: right;;">1600</td><td style="text-align: right;;">1800</td><td style="text-align: right;;">1700</td><td style="text-align: right;;">1900</td><td style="text-align: right;;">1600</td><td style="text-align: right;;">2000</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">21</td><td style="text-align: right;;">21</td><td style="text-align: right;;">1700</td><td style="text-align: right;;">1900</td><td style="text-align: right;;">1800</td><td style="text-align: right;;">2000</td><td style="text-align: right;;">1900</td><td style="text-align: right;;">2100</td><td style="text-align: right;;">1800</td><td style="text-align: right;;">2200</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">22</td><td style="text-align: right;;">22</td><td style="text-align: right;;">1700</td><td style="text-align: right;;">1900</td><td style="text-align: right;;">1800</td><td style="text-align: right;;">2000</td><td style="text-align: right;;">1900</td><td style="text-align: right;;">2100</td><td style="text-align: right;;">1800</td><td style="text-align: right;;">2200</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">23</td><td style="text-align: right;;">23</td><td style="text-align: right;;">1700</td><td style="text-align: right;;">1900</td><td style="text-align: right;;">1800</td><td style="text-align: right;;">2000</td><td style="text-align: right;;">1900</td><td style="text-align: right;;">2100</td><td style="text-align: right;;">1800</td><td style="text-align: right;;">2200</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">24</td><td style="text-align: right;;">24</td><td style="text-align: right;;">1700</td><td style="text-align: right;;">1900</td><td style="text-align: right;;">1800</td><td style="text-align: right;;">2000</td><td style="text-align: right;;">1900</td><td style="text-align: right;;">2100</td><td style="text-align: right;;">1800</td><td style="text-align: right;;">2200</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">25</td><td style="text-align: right;;">25</td><td style="text-align: right;;">1700</td><td style="text-align: right;;">1900</td><td style="text-align: right;;">1800</td><td style="text-align: right;;">2000</td><td style="text-align: right;;">1900</td><td style="text-align: right;;">2100</td><td style="text-align: right;;">1800</td><td style="text-align: right;;">2200</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">26</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">27</td><td style=";">Eaves</td><td style="text-align: right;;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">28</td><td style=";">Span</td><td style="text-align: right;;">25</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">29</td><td style=";">Cladding</td><td style=";">Composite</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">30</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">31</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">32</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">33</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">34</td><td style=";">Cost Per Bay</td><td style="text-align: center;;">1900</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:5.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Summary</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B34</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">(<font color="Red">A3:A25=B28</font>)*(<font color="Red">B1:I1=B27</font>)*(<font color="Red">B2:I2=B29</font>),B3:I25</font>)</td></tr></tbody></table></td></tr></table><br />
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,102
Office Version
365
Platform
Windows
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))
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
44,731
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,316
Messages
5,510,554
Members
408,798
Latest member
jitu20feb

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top