Ignore calculation if text

[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

 

[Stephen]

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

 

[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

 

[AndyB]

Thanks,

No offense, but we did it Stephen's was as he posted 1st.
Regards,
Andy

 

[Pete_UK]

That's okay, but you might like to check out what happens if D4 is
blank.

Pete