VLOOKUP Pricing sheet for E-mail merge

[LizTrinch]

I have a spreadsheet of prices from vendors for products that my company sells that is updated daily. The sheet contains several products with pricing from multiple vendors and multiple locations. (Example: paper, pens and staples sold by Staples, Office Max and Office Depot, out of NY, NJ and CT.) Many of our customers use a set vendor for most days but occasionally call us to see if we can beat their regular vendor's price. We would like to be able to email them with our pricing every day so that we can have their business every time our pricing is best without them having to call us.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
We have a sheet of customers with contact info and common products purchased put together already for a mail merge. At this point, we manually enter the best price from our vendors and a column calculates the price that would go to our customer based on markup and taxes, etc. and we send it out using the mail merge tool. However, our pricing sheet is not ready until about 8 am and the mail merge sheet is not ready until at least 10 am due to the large (and growing) number of customers on it. Many of our customers place their orders first thing the morning so we're losing business.<o></o>
<o></o>
We're hoping for a way to use VLOOKUP to draw from the pricing sheet as soon as it's finished and copied into Sheet2 of the mail merge sheet. The problem is that each customer has different parameters. We are in <?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-comffice:smarttags" /><st1lace w:st="on">New England</st1lace> so we have vendors in several states. Ideally, we need to be able to provide a price for a RI customer based on the lowest price for their product out of one of our RI vendors and provide a price for an MA customer based on the lowest price for their product from any vendor in any NE state. The lowest price is different every day and the locations all have different prices. I'm also a VLOOKUP newbie so I don't know everything that it can do. Currently, I only use it to calculate shipping based on a pricing sheet that is always the same. I choose the product and all other data from the static price sheet comes over. I'm not sure how to create a sheet using VLOOKUP for a sheet that changes every day or one that requires more than one drop down. I'm not sure how to create the second drop down.<o></o>
<o></o>
Also, at this point, the mail merge generates multiple emails for customers that purchase 2 or more products from us. Is there a way to include an array in a mail merge so that their email contains all products they use?<o></o>
<o></o>
I'm not sure if it makes a difference, but each product is not sold out of each location and each vendor so the number of lines per product is different for each product. However, the number of lines per product is always the same day after day and if any vendors or locations are added or removed, we can tweak our merge accordingly. So basically the first 25 rows are always for product A and column A always reads Product A for rows 2-26; the columns that change are the vendor and location columns and of course the price column.

 

[vaskov17]

it would be helpful if you post what your main sheet looks like and what is the expected output from it for a random customer

 

[LizTrinch]

I made a very oversimplified version of my sheet that doesn't violate any laws or company policies about sharing information since these are fake products and prices.

For the mail merge, one customer would be Johnny Appleseed who has a business in Chelsea, MA. So he needs to be qutoed the low price for apples in Massachusetts every day. So I would need the mail merge sheet to have a drop down for state and product. After choosing MA and apple, the corresponding columns should fill in with the vendor with the lowest price and what that price is. Then our calculation column for Johnny Appleseed will update with a formula to add a 5% markup on the Boston Whole Foods' price of 1.10. The email to JA will say that we can promise him a price of 1.16 an apple today if he orders by 3 pm.