Ignore calculation if text
 

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

Ignore calculation if text
 

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

"AndyB" wrote in message
...
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

Ignore calculation if text
 

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

On Nov 29, 11:25 am, AndyB wrote:
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

Ignore calculation if text
 

Thanks,

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

"Pete_UK" wrote:

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

On Nov 29, 11:25 am, AndyB wrote:
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

Ignore calculation if text
 

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

Pete

On Nov 29, 12:16 pm, AndyB wrote:
Thanks,

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



"Pete_UK" wrote:
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

On Nov 29, 11:25 am, AndyB wrote:
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- Hide quoted text -

- Show quoted text -