+ Reply to Thread
Results 1 to 5 of 5

Using VLOOKUP() But "Like" Values

  1. #1
    Registered User
    Join Date
    09-26-2006
    Posts
    9

    Using VLOOKUP() But "Like" Values

    Hi!

    I just want to ask how can I use VLOOKUP() to look a match range to another range but instead of it looking for the EXACT values, I want to look for "LIKE" values. Example:

    Column A
    FIRE
    ICE
    WIND
    WATER

    Column B
    FIRE WORKS
    ICE BERG
    WIND VANE
    WATER BASE

    As you can see, if I use VLOOKUP() in here it wouldn't find any match. What I want is to do is look up Column A to Column B and find matches if their values are similar to each other.


    Thank you.

  2. #2
    Valued Forum Contributor
    Join Date
    07-11-2004
    Posts
    851
    not exactly what you want to do, but this array formula will give you the position of the "similar" item in column B. This assumes column a rows 1-4, and column B rows 1-4 are the ranges, and by "similar" you mean the column A item is the first word in the string IN COLUMN B.

    You could then use this to find and adjacent text or number via offset or index function

    =MATCH(a1,LEFT($b$1:$b$4,LEN(a1)),0)
    not a professional, just trying to assist.....

  3. #3
    Registered User
    Join Date
    09-26-2006
    Posts
    9
    Hi. I tried your suggestion, it doesn't work. I do understand the formula but it doesn't work even if I tweak or adjust it.

  4. #4
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,196
    Quote Originally Posted by charmedcharmer
    Hi. I tried your suggestion, it doesn't work. I do understand the formula but it doesn't work even if I tweak or adjust it.
    it wiorks as

    =OFFSET(B$1,MATCH(A1,LEFT($B$1:$B$8,LEN(A1)),0)-1,0)

    ctrl-shift-enter for Array formula.

    ---

  5. #5
    Registered User
    Join Date
    09-26-2006
    Posts
    9
    OK. Thanks. Ill try this one... Thank you very much.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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