Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 783
  • Last Modified:

Wildcard not working in Excel match statement

I have a match statement in Excel and the wildcard does not work.  Is a wildcard (*) not allowed in a match statement?
0
morinia
Asked:
morinia
  • 3
  • 3
1 Solution
 
cyberkiwiCommented:
No it doesn't.  Are you trying to match any cell that is non-blank, hence the *?
If you can describe what you actually want to do in more detail, we will work out a formula for you.
0
 
zorvek (Kevin Jones)ConsultantCommented:
When using the exact match option with the MATCH function (passing 0 for the third parameter), wildcard characters are allowed. Otherwise wildcard characters are not allowed.

Kevin
0
 
moriniaAdvanced Analytics AnalystAuthor Commented:
Originally I was trying to use this statement to match if the column had "NJ*" in the first two positions followed by any other combination of letters.  It if didn't I would default to "NY".  Since there were only five choices I just put the entire literal in.   I also noticed the "If False" did not work like I wanted it to, so I used iferror to default to New York.

=IFERROR(IF(MATCH(VLOOKUP(INDIRECT($C$1),INFOTAB,2,FALSE),{"NJ*"},0),"NEW JERSEY","NEW YORK"),"NEW YORK")
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
zorvek (Kevin Jones)ConsultantCommented:
You have the MATCH backwards. You are trying to match a value to an array containing wildcard characters. You can't do that. You can only use wildcard characters in the value for which you are searching or the first parameter. This should work:

   =IF(LEFT(VLOOKUP(INDIRECT($C$1),INFOTAB,2,FALSE),2)="NJ","NEW JERSEY","NEW YORK")

Kevin
0
 
moriniaAdvanced Analytics AnalystAuthor Commented:
Kevin,

Where is the wildcard?
0
 
zorvek (Kevin Jones)ConsultantCommented:
You don't need it. You are only testing the first two characters of the lookup value. It's simpler than you thought ;-)

Kevin
0
 
moriniaAdvanced Analytics AnalystAuthor Commented:
Thanks!!  
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now