Lookup in a list using wildcards

[richard_d1]

I am trying to write a formula which checks if a cell value is in a list. So I have:

=ISNUMBER(MATCH(D2,A:A,0))

And this works fine, checking if the value in D2 appears in the list in column A.

However, I want to include wildcards in the list in column A. So for example:

AAA*

AAB*

AAC*

AAD*

AAE*

And then typing a code into D2, it checks whether it begins with one of those strings or not.

Is that possible?

Thanks.

 

[steve the fish]

Try something like this:

=ISNUMBER(MATCH(LEFT(D2,3),A1:A5,0))

but remove the * from the list.

 

[richard_d1]

Try something like this:

=ISNUMBER(MATCH(LEFT(D2,3),A1:A5,0))

but remove the * from the list.

Thanks. That would work.

Not all of them are the first three digits though (sorry, bad example). Is there anything else?

 

[steve the fish]

See if this works:

=ISNUMBER(MATCH(1,SEARCH(A2:A10,LEFT(D2,LEN(A2:A10))),0))

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Lookup in a list using wildcards
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[richard_d1]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Lookup in a list using wildcards
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

Hello,

Sorry; I wasn't aware of this. I'll try to avoid cross-posting in future, but if I do, state that I am doing so.

Thanks for informing me.

 

[richard_d1]

See if this works:

=ISNUMBER(MATCH(1,SEARCH(A2:A10,LEFT(D2,LEN(A2:A10))),0))

That worked! Thanks.