# Price Calculation Problems

1. ## Price Calculation Problems

I am still pretty new to excel, but I am trying to make a spreadsheet that will calculate a price based on a number of variables for my job. I made a series of tables with the variable and the price and now I m trying to figure out how to make it return a price based on user input. so far I have been able to make it work using string of IF formulas, but I am running into some problems and so i am trying to find a more efficient method.

Basically this is how i have it set up.

I have a simple table where one column is a type of material and the next column is a price based on the material next to it ie:

wood 4
plastic 3
cardboard 2

I have a drop down menu (A1) linked to the first column. I am trying to create a simple formula where, depending on which material the drop down cell is, it will return the price next to it. So far I can make it work using a formula similar to the following:

=IF(A1=A2,B2,IF(A1=A3,B3,IF(A1=A4,B4,"")))

This works, but I have some lists with 20+ values so I get stuck with the nested function limit. I managed to work around it, but its sloppy and I am looking for a more efficient formula. if tried a couple of different array formulas but I haven't been able to make any of them work. Ive googled everything I could think of to no avail. It seems like there is probably something simple I'm overlooking, but I haven't found it yet. Could someone point me in a direction at least?

2. ## Re: Price Calculation Problems

Make a table of material and price and the use VLOOKUP

For example, =VLOOKUP(A1, \$G\$1:\$H\$100,2,FALSE)

That uses the value in A1 to search a table in columns G and H and returns the value from the second column if a match is found in the first column. The FALSE says it must be an exact match.

Regards, TMS

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1