Forum Discussion
IF Formula
diidunlop Perhaps the attached file will help you on your way, hoping that I interpreted your question correctly. Created a lookup table with Product and Unit Price (Col H:I). Then I used VLOOKUP in column C to find the price for the product in column B. Column D contains the formula for multiplying Qty and Unit Price. If your Excel version supports dynamic array function you could replace VLOOKUP by XLOOKUP. See column F.
diidunlop I agree with Riny_van_Eekelen 's use of a table to store the prices. However, I'd also want to suggest some redesign of the entire sheet. Normally, one wants to keep a consistent formula all the way down in a column, but your initial design (at least as shown in your original post) does not do that. As a result, using the formulas as done in Riny's spreadsheet--although the formula you requested is exactly what I'd do--because the formula that calculates the totals is included in to the same column, this produces a warning flag as shown in the following screen grab:
In order to best resolve this, though, we need to know what your ultimate use is here. That is, what's the bigger picture into which this fits? Are you wanting to know how to price orders for customers? [That appears to be it.] If so, it could be better to, in effect, create a single invoice sheet--one at a time, not all squeezed in together on one sheet, which would handle the circumstances of some ordering one item, others two or more......The kind of "design" you have now will almost inevitably lead to confusion, if not to errors.
So could I request that you back up and explain the context here?
I've attached a sample of what I was suggesting, building on the solution that Riny_van_Eekelen created (and actually just copying his formula), but now creating a separate sheet that will generate an invoice for up to 9 items per order.
The table of products can be expanded, and the data validation will track with it, as will the calculations. One of the advantages of using a table like Riny gave you: you can change prices on any item on the spot, without revising the formula.
