Results 1 to 5 of 5

Thread: VLOOKUP vs INDEX-MATCH
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    May 2003
    Posts
    387
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VLOOKUP vs INDEX-MATCH

    I use vlookup all the time. For whatever reason it keeps returning #N/A on every cell.
    I can only suspect that the data that was provided is somehow messed up
    I want to try using INDEX MATCH instead of VLOOKUP to see if I get the wanted results.
    I have never use INDEX MATCH and would appreciate assistance

    I need equivalent of the VLOOKUP formula below using INDEX MATCH if someone can assist
    =VLOOKUP(A2,D:H,4,FALSE)

  2. #2
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    33,604
    Post Thanks / Like
    Mentioned
    47 Post(s)
    Tagged
    6 Thread(s)

    Default Re: VLOOKUP vs INDEX-MATCH

    It won't make any difference, but the equivalent would be:

    =INDEX(G:G,MATCH(A2,D:D,0))

    I suspect you have a data type mismatch or leadingt/trailing spaces. Are you looking up data thast looks like numbers?

  3. #3
    Board Regular
    Join Date
    May 2003
    Posts
    387
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VLOOKUP vs INDEX-MATCH

    this is a sample of the data
    C84025681

    all beings with letter C and ends with 8 numbers

  4. #4
    Board Regular
    Join Date
    Jul 2014
    Location
    Memphis, TN
    Posts
    2,627
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VLOOKUP vs INDEX-MATCH

    Sometimes, depending on the source of one of the list, there may be a non-breaking space, CODE (0160), can be entered as SHIFT+SPACE
    If you confirm an expected find exist, check the length of each string. If they look the same and have different LEN results it could be the non-breaking space, which is not removed with TRIM or CLEAN.

    What looks like a space for the WHAT is a SHIFT+SPACE.
    Code:
    Sub Code_160()
    '
    ' Code_160 Macro
    'Removes all Characters of Code 0160 from Active WorkSheet
    'This Character Code is often included in copied or downloaded
    'spreadsheet data that interferes with Excel evaluations.
    'Add to the QAT with suitable Icon. Suggestion is the "Unhappy" Face
    
        Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
    End Sub
    Excel 2013, 2016 with PowerBI
    Knowing that it can be done is half the battle!

  5. #5
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    33,604
    Post Thanks / Like
    Mentioned
    47 Post(s)
    Tagged
    6 Thread(s)

    Default Re: VLOOKUP vs INDEX-MATCH

    Then I'd suspect that you have something like leading or trailing spaces in one set of data and not the other, resulting in no match. Or you have calculation set to manual and filled the formula down so the result of the first formula is copied to every other cell pending a recalculation.

Some videos you may like

User Tag List

Tags for this Thread

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
  •