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:

[TABLE="width: 215"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent"]
Group​
[/TD]
[TD="width: 64, bgcolor: transparent"]
Type​
[/TD]
[TD="width: 81, bgcolor: transparent"]
Day Rate​
[/TD]
[TD="width: 77, bgcolor: transparent"]
Night Rate​
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]
A​
[/TD]
[TD="width: 64, bgcolor: transparent"]
Double​
[/TD]
[TD="width: 81, bgcolor: transparent"]
$450.00​
[/TD]
[TD="width: 77, bgcolor: transparent"]
$125.00​
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]
A​
[/TD]
[TD="width: 64, bgcolor: transparent"]
Single​
[/TD]
[TD="width: 81, bgcolor: transparent"]
$225.00​
[/TD]
[TD="width: 77, bgcolor: transparent"]
$45.00​
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]
B​
[/TD]
[TD="width: 64, bgcolor: transparent"]
Double​
[/TD]
[TD="width: 81, bgcolor: transparent"]
$375.00​
[/TD]
[TD="width: 77, bgcolor: transparent"]
$125.00​
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]
B​
[/TD]
[TD="width: 64, bgcolor: transparent"]
Single​
[/TD]
[TD="width: 81, bgcolor: transparent"]
$150.00​
[/TD]
[TD="width: 77, bgcolor: transparent"]
$45.00​
[/TD]
[/TR]
</tbody>[/TABLE]

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.

[TABLE="width: 215"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent"] Group
[/TD]
[TD="width: 64, bgcolor: transparent"] Type
[/TD]
[TD="width: 81, bgcolor: transparent"] Day Rate
[/TD]
[TD="width: 77, bgcolor: transparent"] Night Rate
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"] B
[/TD]
[TD="width: 64, bgcolor: transparent"] Double
[/TD]
[TD="width: 81, bgcolor: transparent"] Applied
[/TD]
[TD="width: 77, bgcolor: transparent"] Applied
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]
[/TD]
[TD="width: 64"] Rates:
[/TD]
[TD="width: 81"]
$375.00​
[/TD]
[TD="width: 77"]
$125.00​
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 81, bgcolor: transparent"][/TD]
[TD="width: 77, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"] Group
[/TD]
[TD="width: 64, bgcolor: transparent"] Type
[/TD]
[TD="width: 81, bgcolor: transparent"] Day Rate
[/TD]
[TD="width: 77, bgcolor: transparent"] Night Rate
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"] A
[/TD]
[TD="width: 64, bgcolor: transparent"] Single
[/TD]
[TD="width: 81, bgcolor: transparent"] Applied
[/TD]
[TD="width: 77, bgcolor: transparent"] Waived
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]
[/TD]
[TD="width: 64"] Rates:
[/TD]
[TD="width: 81"]
$225.00​
[/TD]
[TD="width: 77"]
$125.00​
[/TD]
[/TR]
</tbody>[/TABLE]

Many thanks,
S

 

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.
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
ADouble BDoubleAppliedApplied
ASingle
BDouble ASingleAppliedWaived
BSingle

<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>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]Group[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]Type[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]Day Rate[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]Night Rate[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]Group[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]Type[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]Day Rate[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]Night Rate[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]2[/TD]

[TD="align: right"]$450.00[/TD]
[TD="align: right"]$125.00[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]3[/TD]

[TD="align: right"]$225.00[/TD]
[TD="align: right"]$45.00[/TD]

[TD="align: right"]$375.00[/TD]
[TD="align: right"]$125.00[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]4[/TD]

[TD="align: right"]$375.00[/TD]
[TD="align: right"]$125.00[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]5[/TD]

[TD="align: right"]$150.00[/TD]
[TD="align: right"]$45.00[/TD]

[TD="align: right"]$225.00[/TD]
[TD="align: right"]$0.00[/TD]

</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 Question
Returning Randomly Selected Value from Table based on Criteria
Replies
4
Views
781
Viau1337
V
R
  • Question Question
Return value based on criteria
Replies
1
Views
518
AhoyNC
A
H
  • Question Question
Match all values within a filtered range based on multiple criteria
Replies
7
Views
1K
MickG
M
K
  • Question Question
Return value from table based on 2 criteria with partial word match
Replies
2
Views
624
kk1352
K
J
  • Question Question
Return next unique value from list based on multiple criteria
2
Replies
15
Views
19K
Marcelo Branco
M

Forum statistics

Threads
1,223,445
Messages
6,172,176
Members
452,446
Latest member
walkman99

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