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

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
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
 
Upvote 0
Mark,

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

Thanks,
gs
 
Upvote 0
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?
 
Upvote 0
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....
 
Upvote 0
=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
 
Upvote 0
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
 
Upvote 0
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"?
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,539
Latest member
alex78

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
Back
Top