Help with match formula

G

New Member
Joined
Aug 19, 2002
Messages
41
=IF(ISNUMBER(MATCH("***"&"*P*",E2,0)),"0","1")

How do I fix this formula to look for both * & P in no particuliar order?

Once again Thank you in advance for your help......
G
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

G

New Member
Joined
Aug 19, 2002
Messages
41
I think I answered my own question, but I am not sure how or why it is working.

Here is what I came up with and it appears to work.

=IF(ISNUMBER(MATCH("?*?"&"*P*",E2,0)),"0","1")

Thanks,
gs
 

G

New Member
Joined
Aug 19, 2002
Messages
41
Mark,

Thank you! Will both of these do the same, is mine correct?

Thanks,
gs
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209

ADVERTISEMENT

On 2002-11-06 11:37, G wrote:
=IF(ISNUMBER(MATCH("***"&"*P*",E2,0)),"0","1")

How do I fix this formula to look for both * & P in no particuliar order?

Once again Thank you in advance for your help......
G

Care to give some examples of E-values that would match the lookup spec?
 

G

New Member
Joined
Aug 19, 2002
Messages
41
Example:
Cell A1 contains: PO01
Cell A2 Contains: *ALP

If the cell contains a * & P then I want to know, however the cell can also contain: *20P,ect....
 

Mark W.

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,654

ADVERTISEMENT

=IF(AND(ISNUMBER(MATCH({"*~**","*P*"},E2,0))),"0","1")

See the Excel Help topics for "Wildcard characters you can use to find text or numbers" and "MATCH worksheet function".

The latter states...

If match_type is 0 and lookup_value is text, lookup_value can contain the wildcard characters, asterisk (*) and question mark (?). An asterisk matches any sequence of characters; a question mark matches any single character.
This message was edited by Mark W. on 2002-11-06 12:14
 

Mark W.

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,654
On 2002-11-06 11:47, G wrote:
Mark,

Thank you! Will both of these do the same, is mine correct?

Thanks,
gs

No, the tilde (~) is needed.
 

G

New Member
Joined
Aug 19, 2002
Messages
41
Mark,

I have looked at that and that is how I was able to get my results, someone else had asked for an example therefore I reply with an example. Can you explain why both your formula & mine formula come out with the same results?

Previous Reply:
I think I answered my own question, but I am not sure how or why it is working.

Here is what I came up with and it appears to work.

=IF(ISNUMBER(MATCH("?*?"&"*P*",E2,0)),"0","1")

Thanks,
gs


Thanks,
g
This message was edited by G on 2002-11-06 12:20
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
On 2002-11-06 12:17, G wrote:
Mark,

I have looked at that and that is how I was able to get my results, someone else had asked for an example therefore I reply with an example. Can you explain why both your formula & mine formula come out with the same results?

Previous Reply:
I think I answered my own question, but I am not sure how or why it is working.

Here is what I came up with and it appears to work.

=IF(ISNUMBER(MATCH("?*?"&"*P*",E2,0)),"0","1")

Thanks,
gs


Thanks,
g
This message was edited by G on 2002-11-06 12:20

Someone else? I believe I have a name... :)

* squeezed in between ?*? appears to loose its special meaning.

By the way,

=IF(AND(ISNUMBER(MATCH({"*~**","*P*"},E2,0))),"0","1")

results in "0" for

P*

and

=IF(ISNUMBER(MATCH("?*?"&"*P*",E2,0)),"0","1")

in "1".

Just curious: Why not just 0 and 1 instead of "0" and "1"?
 

Forum statistics

Threads
1,144,056
Messages
5,722,256
Members
422,419
Latest member
Havok390

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top