Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Dec 2009
    Location
    warren, ohio
    Posts
    66
    Thanks
    1
    Thanked 13 Times in 6 Posts

    problems with vlookup

    I struggle everytime I try to use vlookup or any kind of text search.

    I have two sheets in excel 2007 . sheet 1 Column C contains alpha text. for each row, I want to see if that text is found in sheet 2, column j. It can be only a partial match. I do not want any columns from sheet 2 returned. If found, I want to display FOUND. If not, either blank or MISSING. A #ref or other error message if not found is acceptable. I just need some way to know if the name in sheet 1 row c is anywhere in sheet 2 row j

    =VLOOKUP(C87,Sheet2!$J$2:$J$26,1,FALSE) As I see it , the 1 should give me column K in sheet2(which is blank and I don't want it anyway). Instead, it seems to be giving me sheet 2 column c. But in row 91, where it should not find data in sheet 2, it gives me sheet 2 row 24, column j.
    Attached Files Attached Files
    Last edited by dalejanus; 2015-08-19 at 11:44. Reason: add attachement

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Dale,

    The builtin functions in excel aren't really setup to search partial strings. You also have a problem with the search terms being Upper Case and the items being searched being Mixed Case.

    Here's a User Defined Function (UDF) that should work for you.
    Code:
    Option Explicit
    
    Function zMatchIt(Target As Range) As String
    
      Dim rngSearch  As Range
      Dim rngCell    As Range
      Dim zSrchValue As String
      
      Set rngSearch = Range("SearchRange")
      zSrchValue = UCase(Target.Value)
      zMatchIt = "Missing"
      For Each rngCell In rngSearch.Cells
      
        If (InStr(UCase(rngCell.Value), zSrchValue) > 0) Then
          zMatchIt = "Found"
          Exit For
        End If
        
      Next rngCell
    
        
    End Function  'zMatchIt
    Calling Example: =zmatchit($C78)

    Results:
    dalejanus.JPG

    Test File: finddata.xlsm

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Star Lounger
    Join Date
    Dec 2009
    Location
    warren, ohio
    Posts
    66
    Thanks
    1
    Thanked 13 Times in 6 Posts
    Retiredgeek,
    I was off sick the last few days, but thanks very much for the UDF.
    I have never used one and I'm not sure how your code works. I assume you made some columns into ranges, something I've only done a few times.
    But it does what I need, so I will just copy and paste my data into your sheet.
    In case you haven't guessed, I think spreadsheets are a necessary evil forced upon programmers by accountants and auditors. (I want status. Is that Column E? or $E? or $E$1? sheesh!)

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Dale,

    The SearchRange (rangename) can be defined by Highlighting the Range to be search then typing SearchRange in the Name box and hitting Enter (you MUST hit Enter for it to take. If SearchRange has already been defined you can use the Name manager to delete it or change the cells it refers to.

    As to the calling formula it was designed to be copied down a Column thus the Mixed Reference $C78. If placed in any Column row 78 it will look up the value in $C78 in Sheet2 and return the string it was found in. This formula can now be dragged up or down or left or right and the C will not change but the 78 will (up or down).

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

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