Results 1 to 6 of 6
Like Tree2Likes
  • 1 Post By Ekim
  • 1 Post By skywaz

Vlookup with partial match

This is a discussion on Vlookup with partial match within the Excel Questions forums, part of the Question Forums category; How do you do a vlookup with a partial match?...

  1. #1
    Board Regular
    Join Date
    Jun 2002
    Location
    Hampton, NB, Canada
    Posts
    108

    Default

    How do you do a vlookup with a partial match?

  2. #2
    Board Regular
    Join Date
    Jun 2002
    Location
    Perth, Australia
    Posts
    1,416

    Default

    You can do a partial match using wildcards.

    The asterisk represents the same thing that it did in the good 'ol days of DOS. Similarly, both DOS and Excel will treat the question mark (?) as a wildcard that represents exactly one character.

    In A1:A10, put a list of names (make one of those names Michael).

    In cell C1 put =VLOOKUP(B1,A1:A10,1,FALSE), where B1 is the lookup value.

    To lookup Michael, in B1 put "Mi*" (without the apostrophes).

    An alternative formula is:

    =INDEX(A1:A10,MATCH(B1,A1:A10,0))

    Regards,

    Mike
    LGFN1 likes this.

  3. #3
    New Member
    Join Date
    Sep 2002
    Location
    San Diego, CA
    Posts
    21

    Default

    FYI Spockster, Mike's advice on INDEX MATCH is a good alternative to VLOOKUP. It's much faster on larger spreadsheets.

    =INDEX(A1:A10,MATCH(B1,A1:A10,0))

    Darin
    LGFN1 likes this.

  4. #4
    Board Regular
    Join Date
    Jun 2002
    Location
    Hampton, NB, Canada
    Posts
    108

    Default

    Thanks for the help.

    I have one more question? If i want to look up info in more than one cell, for instants one cell has last name and another cell has first name. How do i do the match using these two cells?

    Thanks once again for all your help

  5. #5
    New Member
    Join Date
    Oct 2013
    Posts
    32

    Default Re: Vlookup with partial match

    Example of formula:
    List of names in column A. First Name is Column A1 and Last Name in Column B1. Paste formula in C1 and drag down. Details to find in columns range D through F. Result to show in column 5 (column E)


    2 Cells
    Code:
    =IFERROR(VLOOKUP(A1&" "&B1,D:F,5,FALSE),"")
    2 Cells with Wild Card
    Code:
    =IFERROR(VLOOKUP(A1&"*"&" "&B1&"*",D:F,5,FALSE),"")
    1 Cell
    Code:
    =IFERROR(VLOOKUP(A1,D:F,5,FALSE),"")
    1 Cell with Wild Card After Name
    Code:
    =IFERROR(VLOOKUP(A1&"*",D:F,5,FALSE),"")
    1 Cell with Wild Card Before and After Name
    Code:
    =IFERROR(VLOOKUP("*"&A1&"*",D:F,5,FALSE),"")

  6. #6
    Board Regular
    Join Date
    Jan 2009
    Location
    Hong Kong
    Posts
    660

    Default Re: Vlookup with partial match

    A simple spreadsheet you can manage could be a better choice than a sophisticated spreadsheet you need help from time to time.
    http://wmfexcel.wordpress.com

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com