IF Function and the use of Wildcards

  • Thread starter Thread starter anon
  • Start date Start date
A

anon

Hi There,

Using data

D1=QL-MUZAC-ZG
H12=15
I12=150
F13=IF(I12<H12,"New Licence is Cheaper","Old Licence is Cheaper")

I'm trying to create a formula using the IF statement, that will show
the value incell I12 where the text in D1 is equal to "QL-?????-Z?"
and if false will perform the following formula "IF(F13="Old Licence
is Cheaper", (I12-(H12*75%)),(I12-(I12*75%)))"

I have managed to get this working, when I match the text in D1
without using wildcards as below
"=IF(D12="QL-MUZAC-ZG",I12,IF(F13="Old Licence is Cheaper",
(I12-(H12*75%)),(I12-(I12*75%))))"

However this doesn't work when I use wildcards as below

=IF(D12="QL-?????-Z?",I12,IF(F13="Old Licence is Cheaper",
(I12-(H12*75%)),(I12-(I12*75%))))

Please help :)
 
Hi
try
=IF(AND(LEFT(D1,3)="QL-",RIGHT(D1,2)="-Z",LEN(D1)
=10),I12,IF(F13="Old Licence
 
Just taking up the condition of the first IF...

=IF(ISNUMBER(SEARCH("QL-?????-Z?",D12)),I12,IF(F13="Old Licence is
Cheaper",(I12-(H12*75%)),(I12-(I12*75%))))

If case is important, replace SEARCH with FIND.
 
I've now got another question regarding this same formula.

Say I want to use the a logic command so SEARCH will check for either
"QL-?????-Z?" OR "QL-?????-9?" how would I add this to the statement?

Aladin said:
Just taking up the condition of the first IF...
Click to expand...
=IF(ISNUMBER(SEARCH("QL-?????-Z?",D12)),I12,IF(F13="Old Licence is
Cheaper",(I12-(H12*75%)),(I12-(I12*75%))))
Click to expand...
 
Hi
one way:
=IF(ISNUMBER(SEARCH("QL-?????-Z?",D12))+ISNUMBER(SEARCH
("QL-?????-9?",D12)),I12,IF(F13="Old Licence is Cheaper",
(I12-(H12*75%)),(I12-(I12*75%))))
 
I've now got another question regarding this same formula.

Say I want to use the a logic command so SEARCH will check for either
"QL-?????-Z?" OR "QL-?????-9?" how would I add this to the statement?
Click to expand...

Also:

=IF(COUNT(SEARCH("QL-?????-Z?",D12),SEARCH("QL-?????-9?",D12)),I12,IF(F13="O
ld Licence is Cheaper",(I12-(H12*75%)),(I12-(I12*75%))))
[..]
 
Back
Top