+ Reply to Thread
Results 1 to 4 of 4

VLOOKUP - Wildcard not working

  1. #1
    Registered User
    Join Date
    05-14-2017
    Location
    Detroit MI
    MS-Off Ver
    Office 365
    Posts
    14

    VLOOKUP - Wildcard not working

    Hi - simple enough - I am trying to do a wildcard match with a vlookup.

    Formula :
    =VLOOKUP("*"&A2&"*",code!A:B,2,0)

    I am getting N/A for results.

    Please help - see attachment. - jerryr0125
    Attached Files Attached Files

  2. #2
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex
    MS-Off Ver
    365
    Posts
    5,774

    Re: VLOOKUP - Wildcard not working

    Wildcards don't work quite like that I'm afraid.

    "*"&A2&"*" in this instance means "any or no text here" & Elevator 456 & "any or no text here".
    As Elevator 456 does not appear in column A of your Sheet2 either with or without text either side of it, it will always return an error.

    BSB

  3. #3
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    17,582

    Re: VLOOKUP - Wildcard not working

    Yes, the wildcard in VLOOKUP works the other way around from what you are thinking. In the example above it is looking for "*Elevator 456*" in column A of sheet code. I think you are trying to figure whether any of the words in column A of code can be found in A2 of desc.

    This is an array formula. After typing in the formula, do not hit ENTER--hit CTRL+SHIFT+ENTER. You have done it correctly if the formula in the formula box has {braces} around it. You cannot type in the braces; they are just an indicator that it is an array formula.

    =INDEX(code!$A$2:$A$4,SUMPRODUCT(ISNUMBER(SEARCH(code!$A$2:$A$4,A2))*ROW($1:$3)))

    See attached.
    Attached Files Attached Files
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  4. #4
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2010
    Posts
    4,695

    Re: VLOOKUP - Wildcard not working

    Or try;

    =LOOKUP(1,-SEARCH(code!$A$2:$A$4,desc!A2),code!$B$2:$B$4)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. MS Query wildcard with parameter NOT working, help please
    By BeauTiesLTD in forum Excel General
    Replies: 1
    Last Post: 06-14-2016, 11:57 AM
  2. Lookup not working with wildcard?
    By mikey42979 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-19-2016, 12:37 PM
  3. Replies: 1
    Last Post: 08-08-2014, 07:53 AM
  4. [SOLVED] Wildcard not working in my COUNTIFS
    By dtrimble in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 12-11-2013, 09:12 PM
  5. LIKE not working with wildcard in MS Query
    By RollerRagerMD in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-03-2013, 10:02 AM
  6. Copy files via wildcard, FileCopy not working
    By koltregaskes in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-22-2011, 10:33 AM
  7. Wildcard Not Working in IF statement
    By jrmccaleb@comcast.net in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-09-2005, 04:08 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1