IF and Vlookup

[Jamki]

Pls help. I'm not sure if I'm thinking this through clearly. Here goes:

QTY/$ Amt
Lemons 20 Qty
Oranges $1,000 Amt
Bananas 30 Qty
Grapes $40 Amt
Cherries 2000 Qty

Pricing
Lemons 500
Bananas 200
Cherries 400

Scenario: I would like a function to say if I there's a Price for lemons do
a vlookup on price and multiply lemons qty against the price for lemon. If
there is no price for grapes, return the price for grape (since there's no
separate pricing for grapes).

 

[Wehrmacher]

I am not sure what your lookup table is or where you would put your results,
but take a look at this. I organized a price table as shown below


Quantity CostAmt
Lemons 20
Oranges $1,000.00
Bananas 30
Grapes $40.00
Cherries 2,000

I used the insert name function to name the rows and columns with the fruit
names and quantities and CostAmt.

I used the following equation to check if a cell was blank and if so created
two possible outcomes. In this particuar case, E9 contains the word
"lemons". So if the above table at lemons quantity were blank, the equation
executes the first option in the if statement, and if not, it executes the
second. You can substitute functions or lookups for the "zero" and "not
zero" entries shown.

=IF(ISBLANK(Quantity INDIRECT(E9)),"zero","not zero")

In this case I created a little table with thr first column with various
fruits (Lemons in E9) and copied it for the rest of the rows and got the
following.

Pricing
Lemons not zero
Oranges zero
Bananas not zero
Grapes zero
Cherries not zero

Hope this helps a little