Ignore calculation if text

  • Thread starter Thread starter AndyB
  • Start date Start date
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
 
Try something like
=IF(VLOOKUP(D4,data!A4:B33,2,FALSE)="ASK","ASK",IF(D4<>"",VLOOKUP(D4,data!A4:B33,2,FALSE))*I4)
 
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
 
Thanks,

No offense, but we did it Stephen's was as he posted 1st.
Regards,
Andy
 
That's okay, but you might like to check out what happens if D4 is
blank.

Pete
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Percentage Increase 13
Sum Products with VLOOKUP 3
Calculation using IF 6
Calculation using IF 1
Excel Vlookup Help 0
How to Stop calculation on one specific sheets only 7
Automatically add a column to summary sheet if another worksheet i 2
copy data from varying slected rows into new spreadsheet 2

Back
Top