Ignore calculation if text

A

AndyB

We have a VLOOKUP fetching prices for various options.
On one option the user has to ask for a specific price.
This means that in our price list we have the text "ASK" against one option.

When the VLOOKUP retrieves a price, we multiply by the exchange rate.
Excel doesn't want to calculate ASK x 0.7.

How can we calculate unless the cell contains ASK in which case it just
returns "ASK"?

=IF(D4<>"",VLOOKUP(D4,data!A4:B33,2,FALSE))*I4

I4 is exchange rate
 
S

Stephen

Try something like
=IF(VLOOKUP(D4,data!A4:B33,2,FALSE)="ASK","ASK",IF(D4<>"",VLOOKUP(D4,data!A4:B33,2,FALSE))*I4)
 
P

Pete_UK

It becomes a bit messy, but you could do this:

=IF(D4="","",IF(VLOOKUP(D4,data!
A4:B33,2,0)="ASK","ASK",VLOOKUP(D4,data!A4:B33,2,0)*I4))

Hope this helps.

Pete
 
Top