I am trying to return information from a data set on the same sheet. I have created a table which shows the cost of sales for different subjects which I put into a drop down list using data validation. I have worked out the costs of approved text books using this formula: =VLOOKUP(A19,costs,4,0)*O2*H20, the result this provides is correct and changes when I select a different subject as it should. This is because they use the same values to calculate the answer. My problem is I need to do the same for photocopying and binding costs of textbooks for each subject which is difficult since they use different values. For example: Business Studies has 250 workbook pages which need photocopying (0.02) and 20 students will have these textbooks so the calculation is basically 250*0.02*20. I am using vlookup here and put *250*20 outside of the brackets meaning that this formula is really specified to business studies when I need it to change for each subject. Is there a way to tell excel to multiply the vlookup by different values in a list using one formula?
Bookmarks