+ Reply to Thread
Results 1 to 8 of 8

lookup and wildcard

  1. #1
    Valued Forum Contributor Blake 7's Avatar
    Join Date
    10-01-2010
    Location
    Bogota, Colombia
    MS-Off Ver
    Excel 2010 64 bit and Excel 2007,
    Posts
    1,373

    lookup and wildcard

    Hi Guys,

    Im trying to see which values contained within col h appear in col b. I have used a lookup with wildcard but it is not returning a value when it should. please see the yellow highlights, these should have been returned.

    Can someone think of a way of overcoming this?

    any ideas?

    Cheers
    Attached Files Attached Files
    Last edited by Blake 7; 02-20-2014 at 11:00 AM.
    Blake 7

    If your question has been answered, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

    If any member's response has helped to solve your problem, please consider using the scales icon on the top right of their post to show your appreciation.

    http://failblog.org/

  2. #2
    Forum Expert RobertMika's Avatar
    Join Date
    06-22-2009
    Location
    Haverhill, UK
    MS-Off Ver
    Excel 2003-13
    Posts
    1,530

    Re: lookup and wildcard

    Those entries are not the same
    Ltd vs Limited

    Try this
    http://www.mrexcel.com/forum/excel-q...planation.html
    If you are http://www.excelforum.com/image.php?type=sigpic&userid=125481&dateline=1392355029happy with the results, please add to the contributor's
    reputation by clicking the reputation icon (star icon).




    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved.
    To undo, select Thread Tools-> Mark thread as Unsolved.
    http://www.excelaris.co.uk

  3. #3
    Forum Guru Izandol's Avatar
    Join Date
    03-29-2012
    Location
    *
    MS-Off Ver
    Excel 20(03|10|13)
    Posts
    2,581

    Re: lookup and wildcard

    These are not matches - "Untha Uk Ltd*" will not match "Untha Uk". Similar problem with "Limited" versus "Ltd". It will be better if you may remove Ltd and Limited from all data you will lookup but I do not know if it will be perfect.
    • Please remember to mark threads Solved with Thread Tools link at top of page.
    • Please use code tags when posting code: [code]Place your code here[/code]
    • Please read Forum Rules

  4. #4
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,904

    Re: lookup and wildcard

    Try this formula. You should get more results.

    =IFERROR(VLOOKUP(LEFT(H2,FIND(" ",H2)-1)&"*",$B$2:$B$6621,1,0),"Not Found")
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  5. #5
    Valued Forum Contributor Blake 7's Avatar
    Join Date
    10-01-2010
    Location
    Bogota, Colombia
    MS-Off Ver
    Excel 2010 64 bit and Excel 2007,
    Posts
    1,373

    Re: lookup and wildcard

    hi guys, sorry if i wasnt clear,, i know that there are differences such as Ltd and Limited and thats why i used the wildcard. I thought the wildcard would work in this case. But nope... can you think of a way around this.

    Cheers

  6. #6
    Valued Forum Contributor Blake 7's Avatar
    Join Date
    10-01-2010
    Location
    Bogota, Colombia
    MS-Off Ver
    Excel 2010 64 bit and Excel 2007,
    Posts
    1,373

    Re: lookup and wildcard

    Hi Alkey! nice one.... brought back more results - cheers

  7. #7
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,904

    Re: lookup and wildcard

    Glad to be of help. Thanks for the feedback!

  8. #8
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,904

    Re: lookup and wildcard

    You can get few more by combining two formulas

    =IFERROR(VLOOKUP(H2&"*",$B$2:$B$6621,1,0),IFERROR(VLOOKUP(LEFT(H2,FIND(" ",H2)-1)&"*",$B$2:$B$6621,1,0),"Not Found"))

+ 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. [SOLVED] Find: Using wildcard, but don't replace with wildcard
    By JimDandy in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-06-2013, 05:39 PM
  2. [SOLVED] Index lookup + multiple entrys + wildcard
    By 20juls in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 01-25-2013, 11:07 AM
  3. Wildcard Lookup Using Excel
    By Shubhachandra in forum Excel General
    Replies: 6
    Last Post: 04-30-2011, 01:54 AM
  4. Wildcard Lookup
    By 353663 in forum Excel - New Users/Basics
    Replies: 7
    Last Post: 10-16-2009, 07:23 AM
  5. Replies: 1
    Last Post: 03-03-2006, 04:10 PM

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