HLOOKUP with multiple variables

[mbla]

<TABLE style="WIDTH: 285pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=380 border=0><COLGROUP><COL style="WIDTH: 93pt; mso-width-source: userset; mso-width-alt: 4534" width=124><COL style="WIDTH: 48pt" span=4 width=64><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD class=xl66 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 285pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" width=380 colSpan=5 height=21>Shipping Discount</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl63 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>Total Price</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">0</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">400</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">800</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">1000</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl63 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>Shipping Discount 1</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">0%</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">5%</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">10%</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">15%</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl63 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>Shipping Discount 2</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">0%</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">10%</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">15%</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">20%</TD></TR></TBODY></TABLE>
In this problem, I have to use the table above and the HLOOKUP function to determine the shipping discount that a number of accounts are to receive based on purchase total price. If the purchase quantity is <350, they get discount #1. If the purchase is >=350, they get discount #2. But I also have to determine the % discount as well. If purchase <400 then they get column 1 %. 400-799 they get Column 2 %, etc...

I need help with a formula that will encompass all of these variables and can be dragged down a column to calculate for several rows. PLEASE HELP!!!!

 

[njimack]

Welcome to the board. Is this what you're after?

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Total Price	0	400	800	1000			PRICE:	500
2	Shipping Discount 1	0%	5%	10%	15%			QTY:	400
3	Shipping Discount 2	0%	10%	15%	20%
4								DISOUNT:	10%
Sheet1

 

[markmzz]

Or try this:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><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: #161120;text-align: center;">1</td><td style=";">Shipping Discount</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=";">Price</td><td style=";">Qtd</td><td style=";">Discount</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Total Price</td><td style="text-align: right;;">0</td><td style="text-align: right;;">400</td><td style="text-align: right;;">800</td><td style="text-align: right;;">1,000</td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td><td style="text-align: right;;">100</td><td style="text-align: right;;">1%</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Shipping Discount 1</td><td style="text-align: right;;">1%</td><td style="text-align: right;;">11%</td><td style="text-align: right;;">111%</td><td style="text-align: right;;">1111%</td><td style="text-align: right;;"></td><td style="text-align: right;;">399</td><td style="text-align: right;;">349</td><td style="text-align: right;;">1%</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Shipping Discount 2</td><td style="text-align: right;;">2%</td><td style="text-align: right;;">22%</td><td style="text-align: right;;">222%</td><td style="text-align: right;;">2222%</td><td style="text-align: right;;"></td><td style="text-align: right;;">400</td><td style="text-align: right;;">350</td><td style="text-align: right;;">22%</td></tr><tr ><td style="color: #161120;text-align: center;">5</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;;">799</td><td style="text-align: right;;">400</td><td style="text-align: right;;">22%</td></tr><tr ><td style="color: #161120;text-align: center;">6</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;;">800</td><td style="text-align: right;;">349</td><td style="text-align: right;;">111%</td></tr><tr ><td style="color: #161120;text-align: center;">7</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;;">999</td><td style="text-align: right;;">350</td><td style="text-align: right;;">222%</td></tr><tr ><td style="color: #161120;text-align: center;">8</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;;">1,000</td><td style="text-align: right;;">400</td><td style="text-align: right;;">2222%</td></tr><tr ><td style="color: #161120;text-align: center;">9</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: #161120;text-align: center;">10</td><td style="text-align: right;;"></td><td style=";">*****</td><td style=";">*****</td><td style=";">*****</td><td style=";">*****</td><td style=";">*****</td><td style=";">*****</td><td style=";">*****</td><td style=";">*****</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Discount</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><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: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><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: #E0E0F0;color: #161120">I2</th><td style="text-align:left">=IF(<font color="Blue">H2<350,LOOKUP(<font color="Red">G2,$B$2:$E$3</font>),LOOKUP(<font color="Red">G2,$B$2:$E$4</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
Markmzz

 

[mbla]

Thanks for the response. That will definitely help. I can make it work with that formula. Do you know how to accomplish the same thing with HLOOKUP?

 

[mbla]

Hey markmzz,

Thanks for the help. That worked perfectly!

 

[markmzz]

Thanks for the response. That will definitely help. I can make it work with that formula. Do you know how to accomplish the same thing with HLOOKUP?

If you want to use HLOOKUP, try this:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><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: #161120;text-align: center;">1</td><td style=";">Shipping Discount</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=";">Price</td><td style=";">Qtd</td><td style=";">Discount</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Total Price</td><td style="text-align: right;;">0</td><td style="text-align: right;;">400</td><td style="text-align: right;;">800</td><td style="text-align: right;;">1,000</td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td><td style="text-align: right;;">100</td><td style="text-align: right;;">1%</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Shipping Discount 1</td><td style="text-align: right;;">1%</td><td style="text-align: right;;">11%</td><td style="text-align: right;;">111%</td><td style="text-align: right;;">1111%</td><td style="text-align: right;;"></td><td style="text-align: right;;">399</td><td style="text-align: right;;">349</td><td style="text-align: right;;">1%</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Shipping Discount 2</td><td style="text-align: right;;">2%</td><td style="text-align: right;;">22%</td><td style="text-align: right;;">222%</td><td style="text-align: right;;">2222%</td><td style="text-align: right;;"></td><td style="text-align: right;;">400</td><td style="text-align: right;;">350</td><td style="text-align: right;;">22%</td></tr><tr ><td style="color: #161120;text-align: center;">5</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;;">799</td><td style="text-align: right;;">400</td><td style="text-align: right;;">22%</td></tr><tr ><td style="color: #161120;text-align: center;">6</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;;">800</td><td style="text-align: right;;">349</td><td style="text-align: right;;">111%</td></tr><tr ><td style="color: #161120;text-align: center;">7</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;;">999</td><td style="text-align: right;;">350</td><td style="text-align: right;;">222%</td></tr><tr ><td style="color: #161120;text-align: center;">8</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;;">1,000</td><td style="text-align: right;;">400</td><td style="text-align: right;;">2222%</td></tr><tr ><td style="color: #161120;text-align: center;">9</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: #161120;text-align: center;">10</td><td style="text-align: right;;"></td><td style=";">*****</td><td style=";">*****</td><td style=";">*****</td><td style=";">*****</td><td style=";">*****</td><td style=";">*****</td><td style=";">*****</td><td style=";">*****</td></tr></tbody></table><p style="width:5.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">DiscountH</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><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: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><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: #E0E0F0;color: #161120">I2</th><td style="text-align:left">=IF(<font color="Blue">H2<350,HLOOKUP(<font color="Red">G2,$B$2:$E$3,2</font>),HLOOKUP(<font color="Red">G2,$B$2:$E$4,3</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

Hey markmzz,

Thanks for the help. That worked perfectly!

And thanks for the feedback.

Markmzz