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?
Bookmarks