Return value from a table based upon multiple criteria

S

Sirod

New Member
Joined
Aug 6, 2009
Messages
47
Hello,

I have a spreadsheet with the following 4 columns – Group,Type, Day Rate, Night Rate. Each row ofeach column has a drop down selection list. Based upon the selections made, I want the spreadsheet to populate theappropriate Day Rate and Night Rate. Data regarding the rates is in a separate table. The table to be used as a reference for therates is as follows:

Group​
Type​
Day Rate​
Night Rate​
A​
Double​
$450.00​
$125.00​
A​
Single​
$225.00​
$45.00​
B​
Double​
$375.00​
$125.00​
B​
Single​
$150.00​
$45.00​
<tbody> </tbody>

Based upon the above table, below are two examples of theresults that I’m looking for. The 4columns will be completed (based upon drop down selections). Below this, I want the appropriate rate valuedisplayed. I’m not sure exactly which formulaI need to accomplish this. Please noarray formulas or VBA.

Group
 Type
 Day Rate
 Night Rate
B
 Double
 Applied
 Applied

 Rates:
$375.00​
$125.00​
Group
 Type
 Day Rate
 Night Rate
A
 Single
 Applied
 Waived

 Rates:
$225.00​
$125.00​
<tbody> </tbody>

Many thanks,
S

 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Sort by date Sort by votes
Try this

<br /><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >Group</td><td >Type</td><td >Day Rate</td><td >Night Rate</td><td > </td><td >Group</td><td >Type</td><td >Day Rate</td><td >Night Rate</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >A</td><td >Double</td><td style="text-align:right; ">$450.00</td><td style="text-align:right; ">$125.00</td><td > </td><td >B</td><td >Double</td><td >Applied</td><td >Applied</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >A</td><td >Single</td><td style="text-align:right; ">$225.00</td><td style="text-align:right; ">$45.00</td><td > </td><td > </td><td > </td><td style="text-align:right; ">375</td><td style="text-align:right; ">125</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >B</td><td >Double</td><td style="text-align:right; ">$375.00</td><td style="text-align:right; ">$125.00</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >B</td><td >Single</td><td style="text-align:right; ">$150.00</td><td style="text-align:right; ">$45.00</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >H3</td><td >=SUMPRODUCT(($A$2:$A$5=$F$2)*($B$2:$B$5=$G$2)*(C2:C5))</td></tr></table></td></tr></table>
 
Upvote 0
Hi Dante,

Thanks for responding.

My apologies, I’ve noticed an error on my post that Ithought that I had corrected. In mysecond example, the Night Rate was waived, and the rate should have been $0.00(not $125). Do you have a suggestion forhow to update the formula to allow for waiving the rate?

Thanks!
 
Upvote 0
Try this

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Group</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Type</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Day Rate</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Night Rate</td><td > </td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Group</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Type</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Day Rate</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Night Rate</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >A</td><td >Double</td><td style="text-align:right; ">$450.00</td><td style="text-align:right; ">$125.00</td><td > </td><td >B</td><td >Double</td><td >Applied</td><td >Applied</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >A</td><td >Single</td><td style="text-align:right; ">$225.00</td><td style="text-align:right; ">$45.00</td><td > </td><td > </td><td > </td><td style="text-align:right; ">$375.00</td><td style="text-align:right; ">$125.00</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >B</td><td >Double</td><td style="text-align:right; ">$375.00</td><td style="text-align:right; ">$125.00</td><td > </td><td >A</td><td >Single</td><td >Applied</td><td >Waived</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >B</td><td >Single</td><td style="text-align:right; ">$150.00</td><td style="text-align:right; ">$45.00</td><td > </td><td > </td><td > </td><td style="text-align:right; ">$225.00</td><td style="text-align:right; ">$0.00</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >H3</td><td >=IF(H2="Waived",0,SUMPRODUCT(($A$2:$A$5=$F2)*($B$2:$B$5=$G2)*(C$2:C$5)))</td></tr></table></td></tr></table>

Copy formula in I3, H5 and I5
 
Upvote 0
DanteAmor said:
Try this

ABCDEFGHI
1GroupTypeDay RateNight Rate GroupTypeDay RateNight Rate
2ADouble$450.00$125.00 BDoubleAppliedApplied
3ASingle$225.00$45.00 $375.00$125.00
4BDouble$375.00$125.00 ASingleAppliedWaived
5BSingle$150.00$45.00 $225.00$0.00

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 76.04px;"><col style="width: 76.04px;"><col style="width: 76.04px;"><col style="width: 76.04px;"><col style="width: 76.04px;"><col style="width: 76.04px;"><col style="width: 76.04px;"><col style="width: 76.04px;"><col style="width: 76.04px;"></colgroup><tbody>
</tbody>

CellFormula
H3=IF(H2="Waived",0,SUMPRODUCT(($A$2:$A$5=$F2)*($B$2:$B$5=$G2)*(C$2:C$5)))

<tbody>
</tbody>

<tbody>
</tbody>


Copy formula in I3, H5 and I5
Click to expand...

This worked perfectly. Many thanks for your assistance.
 
Upvote 0
I'm glad to help you. Thanks for the feedback.
 
Upvote 0
You must log in or register to reply here.

Similar threads

V
  • Question
Returning Randomly Selected Value from Table based on Criteria
Replies
4
Views
615
Viau1337
V
R
  • Question
Return value based on criteria
Replies
1
Views
471
AhoyNC
A
H
  • Question
Match all values within a filtered range based on multiple criteria
Replies
7
Views
1K
MickG
M
K
  • Question
Return value from table based on 2 criteria with partial word match
Replies
2
Views
568
kk1352
K
J
  • Question
Return next unique value from list based on multiple criteria
2
Replies
15
Views
19K
Marcelo Branco
M

Forum statistics

Threads
1,215,942
Messages
6,127,807
Members
449,408
Latest member
Bharathi V

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