Results 1 to 6 of 6

Thread: VLOOKUP returns #NA! on wildcard search

  1. #1

    VLOOKUP returns #NA! on wildcard search



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I'm trying to use the following formula to find a value of SIPK0SIA1 on the cell range bellow but it comes up with #NA!. Any ideas on how to fix this?


    =VLOOKUP("*"&A1&"*",B1:G3,3,FALSE)


    The format for all cells is text and if i search for any of the other field values by placing it in A1 it works. Thanks for your help in advanced.


    # A B C D E F G
    1 SIPK0SIA1 3327710 8421.99.90 LOS DEMÁS 1 Unidades cartucho para desionizador. Y .SIPK0SIA1 SimpliPak 1, Qty Pk 1 SIPK0SIA1
    2 =VLOOKUP("*"&A1&"*",B1:G3,3,FALSE) 3327711 8421.99.90 LOS DEMÁS 1 Unidades cartucho para desionizador. Y .SIMFILTER Simplicity Final Filter, Qty Pk 1 SIMFILTER
    3 3327712 8421.99.90 LOS DEMÁS 1 Unidades Filtro para desionizador. Y .3501012210 Filtros UF, 10,000 lts para desionizador Ultra 373 3501012210

  2. #2
    Super Moderator Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,321
    Articles
    0
    Excel Version
    2010 on Xubuntu
    Perhaps use absolute ranges
    Code:
    =VLOOKUP("*"&A1&"*",$B$1:$G$3,3,FALSE)

  3. #3
    You are looking at a range that the first column contains nothing like the lookup text, so of course you get #N/A?

    What exactly are you expecting, to look in column G? If so, what do you want it to return?

  4. #4
    Quote Originally Posted by Bob Phillips View Post
    You are looking at a range that the first column contains nothing like the lookup text, so of course you get #N/A?

    What exactly are you expecting, to look in column G? If so, what do you want it to return?

    Actually you're wrong the first column contains SIPK0SIA1 which is in column G not once but twice. cartucho para desionizador. Y .SIPK0SIA1 SimpliPak 1, Qty Pk 1 SIPK0SIA1. The correct answer is that because the value i want to return is to the left of the field that vlookup will find the answer in it will not work. Fixed with an index/match formula. Since vlookup only looks to the right it wouldn't return a value even if it found a match in that row. Thanks for trying to help anyways.
    Last edited by theromex; 2013-02-13 at 11:30 AM.

  5. #5
    Super Moderator Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,321
    Articles
    0
    Excel Version
    2010 on Xubuntu
    FYI you can tweak VLOOKUP to lookup to the left

  6. #6
    The first column in your lookup range is B not G, and that doesn't contain that text.

Posting Permissions

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