IF & VLOOKUP with multiple criteria

[J Stacey]

I am trying to create a customer-based sales tool for my brewery business.
I purchase different beers and sell them with 2 options, 'an offer' price and a 'floor' price.


On my Sales worksheet, I have a list of products, using validation (which are positioned in a 3-column table, combined with the 'offer' & 'floor' prices in another worksheet - to which the customer does not see).


I would like to allow the customer to select a Beer product from the list, then enter the amount he currently pays in the next cell, then in the next cell I am looking to create a formula which finds that product he has selected and it's 'offer' price, and reveals if it is more or less than my offer price.


ideally, I'd like the resulting formula to change cell color to green and reveal a message ("we can beat your current price") or turn red with message ("we cannot beat your price") without giving the customer the exact figure.




Is this this clear enough? Please help. It has been an impossible task for me for weeks.

 

[bschwartz]

The only thing I'm not clear on is how the Floor price is factored in. However, focusing for a moment on the Offer price, I created two worksheets. One is the lookup table on a worksheet called Prices, and the other is for the Customer to test the pricing, which is called Sales.

Excel 2010
	A	B
1	Brew	Offer
2	A	5
3	B	7
4	C	4
Prices

Excel 2010
	A	B	C
1	Brew	Your Price	Result
2	A	4	We cannot beat your price.
Sales

Cell Formulas
Range	Formula
C2	=IF(VLOOKUP(A2,Prices!A2:B4,2,0))

Then for the green and red colors, you can use a conditional formatting rule.
1) Select on cell C2, and go to conditional formatting on the home tab --> New Rule --> Use formula
2) Enter this formula: =C2="We can beat your price!"
3) Click Format --> Fill tab --> Green --> Ok, and the Ok again.
4) Create another rule (see step 1)
5) Enter this formula: =C2="We cannot beat your price."
6) Click Format --> Fill tab --> Red. Font tab --> Font color White.
7) Hit Ok, and Ok again.

 

[J Stacey]

Thanks Ben, for such a prompt reply.
The set up, in essence, is along the correct line.

The 'Prices' table is setup up on another worksheet. A list of products with corresponding prices ( i.e. Budweiser $10)
However, the 'Sales, table has a drop-down list of products, (in cell A2), which are sourced from the 'prices' table.
The drop-down list, is essentially the 1st colomn( A2,A3,A4...etc).
so your formula is not exactly calculating correctly.

I would like the customer to choose a product (i.e. Budweiser) from the drop-down list (on the Sales table- A2) and then enter the current price he pays (i.e. $12) and the formula present him with a result.


Thanks again. Pls advise.

 

[bschwartz]

Isn't my formula doing the same thing? Just I am using values A, B, and C as Beer names, since you didn't provide the list of beers. So in A2, the user, in my example, selects A (could work just the same for "Budweiser"), and enter the price he is paying, in cell B2. Then cell B3 looks up the beer type in A2, returns the associated price from the Price sheet, and checks which is greater.

Can you post some sample data, and the specific version of my formula that you used, and tell me what results you are getting?

 

[J Stacey]

My apologies Ben. I must've entered them incorrectly.
Works perfectly. You are a Star!
THANK YOU

 

[bschwartz]

You're very welcome.