VLookUp Help!

[smonczka]

I am trying to put together a pricing wizard for my company that would
allow reps and resellers to easily look up the prices of our products.

I originally designed a pivot table with drop down lists but this
proved to be to difficult for most people to use. I though instead I
could use VLookup to accomplish the same thing. The problem is we
price our products by quantity. 5 to 25 being one price 26 to 50 being
another and so on. Because of the ranges of pricing I can not figure a
way, using VLookup to actually look up a price.

Here is an example of the pricing:

# Part # of Licenses Yrs Price*
AA Product A 00005 - 00025 1 year $5.00 *The number of Licenses
BB Product A 00026 - 00050 2 year $10.00
CC Product B 00051 - 00100 1 year $15.00
DD Product B 00101 - 00250 2 year $20.00
EE Product C 00251 - 00500 1 year $25.00
FF Product C 00501 - 01000 2 year $30.00
GG Product D 01001 - 02000 1 year $35.00
HH Product D 03000 - 05000 2 year $40.00

What I had used was a validation list (of the part names) and
referenced that as the Look Up Value in the VLookup. The user would
pull from this list the Product he was looking for. The VLookup would
then return the Price of the product. The problem is that I have no
way of referencing the number of licenses need of that part number so
that I can return a price. I could list each quantity in the number of
licenses separately but we have 15 products with license ranges going
up to 40k each.

Thanks for any help you can provide with this or if you know of another
way of doing it.

Steve

 

[Mike A]

Try autofilter:

Select your Part Number column by clicking its letter at the top of the
spreadsheet.

On the menu, click Data>Filter>AutoFilter.

Now your column heading will have an arrow which opens a list of all the
part numbers. Select from the list.

 

[smonczka]

Thanks Mike.

 

[smonczka]

Using a cobination of a vlook up and a filter I was able to creat
dropdown list that would look up the pricing of parts based on a name
range. The problem I am now having is that a VLookUp does not seem to
be able to return a null value. In other words if you have a blank
cell in the name rage and you want to be able to look up the cell next
to it (say it had a value of $0.00) using VLookup, well, I cant seem to
be able to do that.

Any ideas?

Thanks,
Steve