+ Reply to Thread
Results 1 to 4 of 4

VLookup if cell contains (rather than matches) a string.

  1. #1
    Registered User
    Join Date
    03-19-2013
    Location
    Cambridge, England
    MS-Off Ver
    Excel 2010
    Posts
    39

    VLookup if cell contains (rather than matches) a string.

    Hi folks, I have a question:

    If Column A contains any number of 5 letter document numbers as follows:
    90521 90876
    78431
    97863 86452 13576 98761 84521
    etc.

    And Column B contains the location of those documents which I would like to return:
    Location A
    Location D
    Location E
    etc.

    How can I return the location of a given document (e.g.97863) in a cell?

    A vlookup or index/match would only find my document if it matched what's in column A exactly and I want it if the row contains.
    i.e. I can find document 78431 by using =VLOOKUP(78431,$A$1:$B$3,2,FALSE), or =INDEX(B1:B3,MATCH(78431,A1:A3,0),1) but this doesn't work if there is more than one document in the location (e.g. document 97863).

    Any suggestions?

  2. #2
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,842

    Re: VLookup if cell contains (rather than matches) a string.

    You could try this:

    =VLOOKUP("*"&78431&"*",$A$1:$B$3,2,0)

    But it will only return the first match it finds.

    - Moo

  3. #3
    Registered User
    Join Date
    03-19-2013
    Location
    Cambridge, England
    MS-Off Ver
    Excel 2010
    Posts
    39

    Re: VLookup if cell contains (rather than matches) a string.

    Just what I needed and so simple! Thank you!

  4. #4
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,842

    Re: VLookup if cell contains (rather than matches) a string.

    You're welcome. Glad to help. =)

    - Moo

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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