Returning a value using multiple criteria

R

RobNSB

New Member
Joined
Jul 8, 2019
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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

[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 936"]
<colgroup><col width="104" span="9" style="width:78pt"> </colgroup><tbody>[TR]
[TD="class: xl72, width: 104"]Eaves m>[/TD]
[TD="class: xl74, width: 104"]3[/TD]
[TD="class: xl73, width: 104"]3[/TD]
[TD="class: xl74, width: 104"]4[/TD]
[TD="class: xl73, width: 104"]4[/TD]
[TD="class: xl74, width: 104"]5[/TD]
[TD="class: xl73, width: 104"]5[/TD]
[TD="class: xl74, width: 104"]6[/TD]
[TD="class: xl73, width: 104"]6[/TD]
[/TR]
[TR]
[TD="class: xl71"]Gable m[/TD]
[TD="class: xl68"]SingleSkin[/TD]
[TD="class: xl68"]Composite[/TD]
[TD="class: xl68"]SingleSkin[/TD]
[TD="class: xl68"]Composite[/TD]
[TD="class: xl68"]SingleSkin[/TD]
[TD="class: xl68"]Composite[/TD]
[TD="class: xl68"]SingleSkin[/TD]
[TD="class: xl68"]Composite[/TD]
[/TR]
[TR]
[TD="class: xl71"]3[/TD]
[TD="class: xl67"]1100[/TD]
[TD="class: xl67"]1300[/TD]
[TD="class: xl67"]1200[/TD]
[TD="class: xl67"]1400[/TD]
[TD="class: xl67"]1300[/TD]
[TD="class: xl67"]1500[/TD]
[TD="class: xl67"]1400[/TD]
[TD="class: xl67"]1600[/TD]
[/TR]
[TR]
[TD="class: xl71"]4[/TD]
[TD="class: xl66"]1100[/TD]
[TD="class: xl66"]1300[/TD]
[TD="class: xl66"]1200[/TD]
[TD="class: xl66"]1400[/TD]
[TD="class: xl66"]1300[/TD]
[TD="class: xl66"]1500[/TD]
[TD="class: xl66"]1400[/TD]
[TD="class: xl66"]1600[/TD]
[/TR]
[TR]
[TD="class: xl71"]5[/TD]
[TD="class: xl66"]1100[/TD]
[TD="class: xl66"]1300[/TD]
[TD="class: xl66"]1200[/TD]
[TD="class: xl66"]1400[/TD]
[TD="class: xl66"]1300[/TD]
[TD="class: xl66"]1500[/TD]
[TD="class: xl66"]1400[/TD]
[TD="class: xl66"]1600[/TD]
[/TR]
[TR]
[TD="class: xl71"]6[/TD]
[TD="class: xl66"]1100[/TD]
[TD="class: xl66"]1300[/TD]
[TD="class: xl66"]1200[/TD]
[TD="class: xl66"]1400[/TD]
[TD="class: xl66"]1300[/TD]
[TD="class: xl66"]1500[/TD]
[TD="class: xl66"]1400[/TD]
[TD="class: xl66"]1600[/TD]
[/TR]
[TR]
[TD="class: xl71"]7[/TD]
[TD="class: xl66"]1100[/TD]
[TD="class: xl66"]1300[/TD]
[TD="class: xl66"]1200[/TD]
[TD="class: xl66"]1400[/TD]
[TD="class: xl66"]1300[/TD]
[TD="class: xl66"]1500[/TD]
[TD="class: xl66"]1400[/TD]
[TD="class: xl66"]1600[/TD]
[/TR]
[TR]
[TD="class: xl71"]8[/TD]
[TD="class: xl66"]1100[/TD]
[TD="class: xl66"]1300[/TD]
[TD="class: xl66"]1200[/TD]
[TD="class: xl66"]1400[/TD]
[TD="class: xl66"]1300[/TD]
[TD="class: xl66"]1500[/TD]
[TD="class: xl66"]1400[/TD]
[TD="class: xl66"]1600[/TD]
[/TR]
[TR]
[TD="class: xl71"]9[/TD]
[TD="class: xl66"]1100[/TD]
[TD="class: xl66"]1300[/TD]
[TD="class: xl66"]1200[/TD]
[TD="class: xl66"]1400[/TD]
[TD="class: xl66"]1300[/TD]
[TD="class: xl66"]1500[/TD]
[TD="class: xl66"]1400[/TD]
[TD="class: xl66"]1600[/TD]
[/TR]
[TR]
[TD="class: xl71"]10[/TD]
[TD="class: xl66"]1100[/TD]
[TD="class: xl66"]1300[/TD]
[TD="class: xl66"]1200[/TD]
[TD="class: xl66"]1400[/TD]
[TD="class: xl66"]1300[/TD]
[TD="class: xl66"]1500[/TD]
[TD="class: xl66"]1400[/TD]
[TD="class: xl66"]1600[/TD]
[/TR]
[TR]
[TD="class: xl71"]11[/TD]
[TD="class: xl66"]1300[/TD]
[TD="class: xl66"]1500[/TD]
[TD="class: xl66"]1400[/TD]
[TD="class: xl66"]1600[/TD]
[TD="class: xl66"]1500[/TD]
[TD="class: xl66"]1700[/TD]
[TD="class: xl66"]1600[/TD]
[TD="class: xl66"]1800[/TD]
[/TR]
[TR]
[TD="class: xl71"]12[/TD]
[TD="class: xl66"]1300[/TD]
[TD="class: xl66"]1500[/TD]
[TD="class: xl66"]1400[/TD]
[TD="class: xl66"]1600[/TD]
[TD="class: xl66"]1500[/TD]
[TD="class: xl66"]1700[/TD]
[TD="class: xl66"]1600[/TD]
[TD="class: xl66"]1800[/TD]
[/TR]
[TR]
[TD="class: xl71"]13[/TD]
[TD="class: xl66"]1300[/TD]
[TD="class: xl66"]1500[/TD]
[TD="class: xl66"]1400[/TD]
[TD="class: xl66"]1600[/TD]
[TD="class: xl66"]1500[/TD]
[TD="class: xl66"]1700[/TD]
[TD="class: xl66"]1600[/TD]
[TD="class: xl66"]1800[/TD]
[/TR]
[TR]
[TD="class: xl71"]14[/TD]
[TD="class: xl66"]1300[/TD]
[TD="class: xl66"]1500[/TD]
[TD="class: xl66"]1400[/TD]
[TD="class: xl66"]1600[/TD]
[TD="class: xl66"]1500[/TD]
[TD="class: xl66"]1700[/TD]
[TD="class: xl66"]1600[/TD]
[TD="class: xl66"]1800[/TD]
[/TR]
[TR]
[TD="class: xl71"]15[/TD]
[TD="class: xl66"]1300[/TD]
[TD="class: xl66"]1500[/TD]
[TD="class: xl66"]1400[/TD]
[TD="class: xl66"]1600[/TD]
[TD="class: xl66"]1500[/TD]
[TD="class: xl66"]1700[/TD]
[TD="class: xl66"]1600[/TD]
[TD="class: xl66"]1800[/TD]
[/TR]
[TR]
[TD="class: xl71"]16[/TD]
[TD="class: xl66"]1500[/TD]
[TD="class: xl66"]1700[/TD]
[TD="class: xl66"]1600[/TD]
[TD="class: xl66"]1800[/TD]
[TD="class: xl66"]1700[/TD]
[TD="class: xl66"]1900[/TD]
[TD="class: xl66"]1600[/TD]
[TD="class: xl66"]2000[/TD]
[/TR]
[TR]
[TD="class: xl71"]17[/TD]
[TD="class: xl66"]1500[/TD]
[TD="class: xl66"]1700[/TD]
[TD="class: xl66"]1600[/TD]
[TD="class: xl66"]1800[/TD]
[TD="class: xl66"]1700[/TD]
[TD="class: xl66"]1900[/TD]
[TD="class: xl66"]1600[/TD]
[TD="class: xl66"]2000[/TD]
[/TR]
[TR]
[TD="class: xl71"]18[/TD]
[TD="class: xl66"]1500[/TD]
[TD="class: xl66"]1700[/TD]
[TD="class: xl66"]1600[/TD]
[TD="class: xl66"]1800[/TD]
[TD="class: xl66"]1700[/TD]
[TD="class: xl66"]1900[/TD]
[TD="class: xl66"]1600[/TD]
[TD="class: xl66"]2000[/TD]
[/TR]
[TR]
[TD="class: xl71"]19[/TD]
[TD="class: xl66"]1500[/TD]
[TD="class: xl66"]1700[/TD]
[TD="class: xl66"]1600[/TD]
[TD="class: xl66"]1800[/TD]
[TD="class: xl66"]1700[/TD]
[TD="class: xl66"]1900[/TD]
[TD="class: xl66"]1600[/TD]
[TD="class: xl66"]2000[/TD]
[/TR]
[TR]
[TD="class: xl71"]20[/TD]
[TD="class: xl66"]1500[/TD]
[TD="class: xl66"]1700[/TD]
[TD="class: xl66"]1600[/TD]
[TD="class: xl66"]1800[/TD]
[TD="class: xl66"]1700[/TD]
[TD="class: xl66"]1900[/TD]
[TD="class: xl66"]1600[/TD]
[TD="class: xl66"]2000[/TD]
[/TR]
[TR]
[TD="class: xl71"]21[/TD]
[TD="class: xl66"]1700[/TD]
[TD="class: xl66"]1900[/TD]
[TD="class: xl66"]1800[/TD]
[TD="class: xl66"]2000[/TD]
[TD="class: xl66"]1900[/TD]
[TD="class: xl66"]2100[/TD]
[TD="class: xl66"]1800[/TD]
[TD="class: xl66"]2200[/TD]
[/TR]
[TR]
[TD="class: xl71"]22[/TD]
[TD="class: xl66"]1700[/TD]
[TD="class: xl66"]1900[/TD]
[TD="class: xl66"]1800[/TD]
[TD="class: xl66"]2000[/TD]
[TD="class: xl66"]1900[/TD]
[TD="class: xl66"]2100[/TD]
[TD="class: xl66"]1800[/TD]
[TD="class: xl66"]2200[/TD]
[/TR]
[TR]
[TD="class: xl71"]23[/TD]
[TD="class: xl66"]1700[/TD]
[TD="class: xl66"]1900[/TD]
[TD="class: xl66"]1800[/TD]
[TD="class: xl66"]2000[/TD]
[TD="class: xl66"]1900[/TD]
[TD="class: xl66"]2100[/TD]
[TD="class: xl66"]1800[/TD]
[TD="class: xl66"]2200[/TD]
[/TR]
[TR]
[TD="class: xl71"]24[/TD]
[TD="class: xl66"]1700[/TD]
[TD="class: xl66"]1900[/TD]
[TD="class: xl66"]1800[/TD]
[TD="class: xl66"]2000[/TD]
[TD="class: xl66"]1900[/TD]
[TD="class: xl66"]2100[/TD]
[TD="class: xl66"]1800[/TD]
[TD="class: xl66"]2200[/TD]
[/TR]
[TR]
[TD="class: xl71"]25[/TD]
[TD="class: xl65"]1700[/TD]
[TD="class: xl65"]1900[/TD]
[TD="class: xl65"]1800[/TD]
[TD="class: xl65"]2000[/TD]
[TD="class: xl65"]1900[/TD]
[TD="class: xl65"]2100[/TD]
[TD="class: xl65"]1800[/TD]
[TD="class: xl65"]2200[/TD]
[/TR]
[TR]
[TD="class: xl69"] [/TD]
[TD="class: xl69"] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl68"]Eaves[/TD]
[TD="class: xl68"]6[/TD]
[TD="class: xl80"][/TD]
[TD="class: xl80"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl68"]Span[/TD]
[TD="class: xl68"]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl68"]Cladding[/TD]
[TD="class: xl68"]Composite[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl68"][/TD]
[TD="class: xl68"][/TD]
[TD][/TD]
[TD="class: xl79, colspan: 2"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl68"][/TD]
[TD="class: xl68"][/TD]
[TD][/TD]
[TD="class: xl78"][/TD]
[TD="class: xl78"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl70"] [/TD]
[TD="class: xl70"] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl76"]Cost Per Bay[/TD]
[TD="class: xl77, align: center"]#VALUE![/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Any help would be gratefully received

Thank you
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Sort by date Sort by votes
Hi & welcome to MrExcel
How about


Book1
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
Cell Formulas
RangeFormula
B34=SUMPRODUCT((A3:A25=B28)*(B1:I1=B27)*(B2:I2=B29),B3:I25)
 
Upvote 0
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))
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Sumifs - Using Multiple Criteria and Multiple Values
Replies
7
Views
351
NickFru
N
P
  • Question Question
Populating, aggregating and sorting multiple matching values in Excel
Replies
0
Views
220
Pbobas
P
S
  • Question Question
Splitting text into only two cells ?
Replies
4
Views
442
Peter_SSs
Peter_SSs
J
  • Question Question
Macro for copying data from multiple sheets to a common sheet based on a cell value
Replies
0
Views
416
jmk15315
J
T
  • Question Question
Running Average of last n row if sum is between a certain value
Replies
1
Views
72
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,824
Messages
6,181,187
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top