3 Column VLOOKUP Table?

[ashleynjones]

Here's what I have:

<TABLE style="WIDTH: 192pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=256><COLGROUP><COL style="WIDTH: 48pt" span=4 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 width=64>

Customer​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>

Discount​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>

Total​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>

Cases ​

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>Ashley</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>2</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>Greg</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>4</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>Marsha</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>3</TD></TR></TBODY></TABLE>

Discount varies depending on the #of cases ordered by the customer. Total would be cases*cost-discount.

How can I create a vlookup that would read: If Ashley orders 2 cases, her discount will be $3.50.

Like I mentioned earlier, not all customers will get a discount, and the discount relies on how many cases are being ordered.

If you need more information, I'm happy to help. I have been trying to figure this out for a few hours now and my brain is a little fried.

 

[Dryver14]

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>AR</TD><TD>AS</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">£0.00</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">£0.00</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">£3.60</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">£4.20</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right">£4.80</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="TEXT-ALIGN: right">5</TD><TD style="TEXT-ALIGN: right">£5.40</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="TEXT-ALIGN: right">6</TD><TD style="TEXT-ALIGN: right">£6.20</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="TEXT-ALIGN: right">7</TD><TD style="TEXT-ALIGN: right">£7.00</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD style="TEXT-ALIGN: right">8</TD><TD style="TEXT-ALIGN: right">£</TD></TR></TBODY></TABLE>


something like this

create a table out of the way in this case I have used AR3:AS11

then assuming first discount column is B2 use this formula

=VLOOKUP(D2,$AR$3:$AS$11,2,0)

 

[ashleynjones]

How will this table ensure that not all customers get the same discount. For instance, Greg doesn't get a discount when he orders, but Ashley does.

 

[Dryver14]

sorry did not quite get that bit, I thought the none discount was based on the amount purchased not just the name In that case you probably need a sumproduct formula which i am not great with or index and match

create a table like this

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>AQ</TD><TD>AR</TD><TD>AS</TD><TD>AT</TD><TD>AU</TD><TD>AV</TD><TD>AW</TD><TD>AX</TD><TD>AY</TD><TD>AZ</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD> </TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right">5</TD><TD style="TEXT-ALIGN: right">6</TD><TD style="TEXT-ALIGN: right">7</TD><TD style="TEXT-ALIGN: right">8</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD>dave</TD><TD style="TEXT-ALIGN: right">13.00</TD><TD style="TEXT-ALIGN: right">10.00</TD><TD style="TEXT-ALIGN: right">0.00</TD><TD style="TEXT-ALIGN: right">11.00</TD><TD style="TEXT-ALIGN: right">13.00</TD><TD style="TEXT-ALIGN: right">2.00</TD><TD style="TEXT-ALIGN: right">9.00</TD><TD style="TEXT-ALIGN: right">5.00</TD><TD style="TEXT-ALIGN: right">4.00</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD>steve</TD><TD style="TEXT-ALIGN: right">0.00</TD><TD style="TEXT-ALIGN: right">5.00</TD><TD style="TEXT-ALIGN: right">1.00</TD><TD style="TEXT-ALIGN: right">5.00</TD><TD style="TEXT-ALIGN: right">9.00</TD><TD style="TEXT-ALIGN: right">10.00</TD><TD style="TEXT-ALIGN: right">11.00</TD><TD style="TEXT-ALIGN: right">8.00</TD><TD style="TEXT-ALIGN: right">3.00</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD>fred</TD><TD style="TEXT-ALIGN: right">4.00</TD><TD style="TEXT-ALIGN: right">2.00</TD><TD style="TEXT-ALIGN: right">14.00</TD><TD style="TEXT-ALIGN: right">6.00</TD><TD style="TEXT-ALIGN: right">10.00</TD><TD style="TEXT-ALIGN: right">8.00</TD><TD style="TEXT-ALIGN: right">0.00</TD><TD style="TEXT-ALIGN: right">6.00</TD><TD style="TEXT-ALIGN: right">10.00</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD>mark</TD><TD style="TEXT-ALIGN: right">12.00</TD><TD style="TEXT-ALIGN: right">7.00</TD><TD style="TEXT-ALIGN: right">4.00</TD><TD style="TEXT-ALIGN: right">6.00</TD><TD style="TEXT-ALIGN: right">14.00</TD><TD style="TEXT-ALIGN: right">11.00</TD><TD style="TEXT-ALIGN: right">5.00</TD><TD style="TEXT-ALIGN: right">1.00</TD><TD style="TEXT-ALIGN: right">14.00</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD>tony</TD><TD style="TEXT-ALIGN: right">2.00</TD><TD style="TEXT-ALIGN: right">5.00</TD><TD style="TEXT-ALIGN: right">7.00</TD><TD style="TEXT-ALIGN: right">7.00</TD><TD style="TEXT-ALIGN: right">0.00</TD><TD style="TEXT-ALIGN: right">12.00</TD><TD style="TEXT-ALIGN: right">5.00</TD><TD style="TEXT-ALIGN: right">6.00</TD><TD style="TEXT-ALIGN: right">14.00</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD>geoff</TD><TD style="TEXT-ALIGN: right">14.00</TD><TD style="TEXT-ALIGN: right">2.00</TD><TD style="TEXT-ALIGN: right">2.00</TD><TD style="TEXT-ALIGN: right">10.00</TD><TD style="TEXT-ALIGN: right">0.00</TD><TD style="TEXT-ALIGN: right">10.00</TD><TD style="TEXT-ALIGN: right">1.00</TD><TD style="TEXT-ALIGN: right">3.00</TD><TD style="TEXT-ALIGN: right">3.00</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD>carol</TD><TD style="TEXT-ALIGN: right">9.00</TD><TD style="TEXT-ALIGN: right">9.00</TD><TD style="TEXT-ALIGN: right">14.00</TD><TD style="TEXT-ALIGN: right">13.00</TD><TD style="TEXT-ALIGN: right">7.00</TD><TD style="TEXT-ALIGN: right">4.00</TD><TD style="TEXT-ALIGN: right">15.00</TD><TD style="TEXT-ALIGN: right">11.00</TD><TD style="TEXT-ALIGN: right">10.00</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD>jane</TD><TD style="TEXT-ALIGN: right">2.00</TD><TD style="TEXT-ALIGN: right">3.00</TD><TD style="TEXT-ALIGN: right">9.00</TD><TD style="TEXT-ALIGN: right">1.00</TD><TD style="TEXT-ALIGN: right">12.00</TD><TD style="TEXT-ALIGN: right">4.00</TD><TD style="TEXT-ALIGN: right">10.00</TD><TD style="TEXT-ALIGN: right">1.00</TD><TD style="TEXT-ALIGN: right">7.00</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD>sarah</TD><TD style="TEXT-ALIGN: right">4.00</TD><TD style="TEXT-ALIGN: right">15.00</TD><TD style="TEXT-ALIGN: right">5.00</TD><TD style="TEXT-ALIGN: right">2.00</TD><TD style="TEXT-ALIGN: right">12.00</TD><TD style="TEXT-ALIGN: right">12.00</TD><TD style="TEXT-ALIGN: right">8.00</TD><TD style="TEXT-ALIGN: right">6.00</TD><TD style="TEXT-ALIGN: right">12.00</TD></TR></TBODY></TABLE>
then formula in B2

=INDEX($AR$3:$AZ$11,MATCH(A2,$AQ$3:$AQ$11,0),MATCH(D2,$AR$2:$AZ$2,0))

I've just generated some random numbers here but hopefully you get the picture AR3:AZ11 is the area with the results

 

[scottylad2]

Slight alteration to your layout, I'm assuming those getting a discount will be known to you so store their names in a list somewhere and something like this will work

Excel Workbook
	D	E	F	G	H	I	J
12	Customer	Discount	Price p/Case	Cases	Total		Discound list
13	Ashley	3.5	6.99	2	10.48		Ashley
14	Greg	3.5	6.99	2	13.98		Marsha
15	Marsha	3.5	6.99	2	10.48
Sheet1

entered with Ctrl+Shift+Enter