+ Reply to Thread
Results 1 to 29 of 29

Problem with VLookup and Index Match on mixed Text and Numeric codes

  1. #1
    Forum Contributor
    Join Date
    01-28-2010
    Location
    Pittsburgh, PA
    MS-Off Ver
    Excel 2013
    Posts
    200

    Problem with VLookup and Index Match on mixed Text and Numeric codes

    Hello,

    I'm trying to look up brands using codes that look like this:
    001200
    001200BT
    0002M
    etc.

    I tried to convert everything to text, but it only seems to be converting the numeric only to text (i.e. the 001200 to text and NOT the 0002M), at least by what i see in the little error check that is popping up. I think consequently my lookup is pulling N/A rather than matching it. I've used both index/match and vlookup with the same issue.

    What am I doing wrong? If I'm doing a lookup across another table, what's the best format for the above type characters to be in for a proper lookup?

    Thanks!

  2. #2
    Forum Contributor
    Join Date
    03-12-2009
    Location
    Calgary, Canada
    MS-Off Ver
    Excel 365
    Posts
    236

    Re: Problem with VLookup and Index Match on mixed Text and Numeric codes

    Can you keep it in general? If you need it to lead with zeros, just start with an apostrophe. (i.e. '0002M)
    If you can't figure out how a formula works, try stepping through it using "Evaluate Formula" in the Formula Auditing menu item in the tools menu!

    If you want to see where your code went wrong, try stepping through it by clicking in the code and pressing F8 and watch as the magic happens!


    If you are happy with any of the results, please add to the contributor's reputation by clicking the star icon.

  3. #3
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: Problem with VLookup and Index Match on mixed Text and Numeric codes

    A sample workbook would be helpful to know the reason why you are getting #N/A.
    Regards
    sktneer


    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.

  4. #4
    Forum Contributor
    Join Date
    01-28-2010
    Location
    Pittsburgh, PA
    MS-Off Ver
    Excel 2013
    Posts
    200

    Re: Problem with VLookup and Index Match on mixed Text and Numeric codes

    It's a bit flaky with the general format. Looks up some and not others.

    Is it tough to add that apostrophe to 140k rows of data?

  5. #5
    Forum Contributor
    Join Date
    01-28-2010
    Location
    Pittsburgh, PA
    MS-Off Ver
    Excel 2013
    Posts
    200

    Re: Problem with VLookup and Index Match on mixed Text and Numeric codes

    Ok let me try and post a truncated version since someone asked the same thing. It's all the SKUs for a particular company so wanted to be careful not to get out in the open.

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Problem with VLookup and Index Match on mixed Text and Numeric codes

    Quote Originally Posted by Vaslo View Post
    I tried to convert everything to text, but it only seems to be converting the numeric only to text (i.e. the 001200 to text and NOT the 0002M), at least by what i see in the little error check that is popping up. I think consequently my lookup is pulling N/A rather than matching it. I've used both index/match and vlookup with the same issue.
    Any entries with alpha as well as numeric are text by default. Excel only used numeric (any of them) for entries that are actually numbers/values, and sometimes, even if it looks like a number, excel has made it text. For instance, 31/12/2013 looks like a date, but if you are using the US date format, excel will make that text...on the other hand, 12/31/2013, with the US date format IS a number.

    So, converting text to text will have no affect, and if you have a "number" like 001200, chances are, that is also already text.

    If you know for certain that a value you are searching for, really does exist in the other table, check for leading/trailing spaces on both entries. Test with sheet1!A1=Sheet2!A1 (adjust references, obviously)...TRUE means they are identical, FALSE means they are not - so you can then start checking to find the difference
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  7. #7
    Forum Contributor
    Join Date
    01-28-2010
    Location
    Pittsburgh, PA
    MS-Off Ver
    Excel 2013
    Posts
    200

    Re: Problem with VLookup and Index Match on mixed Text and Numeric codes

    All,

    Per request, here is a sample with what I hope is my problem. As you can see, my index/match is not working. This is usually about 250k lines of data looking up across 100k+ SKUs. Please give me your opinions or ideas. Better ways to look up? Suggestions on how to format?


    FDibbins, I tried your method but it's coming up false.

    Thanks!!Inv Summary Check File - online.xlsx

  8. #8
    Forum Contributor
    Join Date
    01-28-2010
    Location
    Pittsburgh, PA
    MS-Off Ver
    Excel 2013
    Posts
    200

    Re: Problem with VLookup and Index Match on mixed Text and Numeric codes

    And FD to your point they are NOT equal since coming up false. Though they are set to the same type. so unsure of the problem.

  9. #9
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: Problem with VLookup and Index Match on mixed Text and Numeric codes

    May be this........

    Please Login or Register  to view this content.
    See if this helps.

  10. #10
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Problem with VLookup and Index Match on mixed Text and Numeric codes

    The SKU's on Data are text, not values/numbers (you can see this by the small green triangle in the top-left of each cell), the SKU's in your table are values. change your formula to...
    =INDEX(ZoneCategory,MATCH(D2*1,sku,0))

  11. #11
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Problem with VLookup and Index Match on mixed Text and Numeric codes

    have you tried
    =VLOOKUP(A3&"",Data!$D$2:$E$5,2,FALSE)
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  12. #12
    Forum Contributor
    Join Date
    01-28-2010
    Location
    Pittsburgh, PA
    MS-Off Ver
    Excel 2013
    Posts
    200

    Re: Problem with VLookup and Index Match on mixed Text and Numeric codes

    Quote Originally Posted by FDibbins View Post
    The SKU's on Data are text, not values/numbers (you can see this by the small green triangle in the top-left of each cell), the SKU's in your table are values. change your formula to...
    =INDEX(ZoneCategory,MATCH(D2*1,sku,0))
    Probably should have shown but I have some SKUs with letters so I don't think that will work.

  13. #13
    Forum Contributor
    Join Date
    01-28-2010
    Location
    Pittsburgh, PA
    MS-Off Ver
    Excel 2013
    Posts
    200

    Re: Problem with VLookup and Index Match on mixed Text and Numeric codes

    Quote Originally Posted by martindwilson View Post
    have you tried
    =VLOOKUP(A3&"",Data!$D$2:$E$5,2,FALSE)
    It's 200k lines, won't the vlookup be more bulky than index/match? Thats what I've heard, and it's already a long process to refresh due to all the lines.

  14. #14
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Problem with VLookup and Index Match on mixed Text and Numeric codes

    Quote Originally Posted by Vaslo View Post
    Probably should have shown but I have some SKUs with letters so I don't think that will work.
    I agree with that statement..

    Can you post a sheet with a more diverse sample set of data that includes all possible scenarios?

  15. #15
    Forum Contributor
    Join Date
    01-28-2010
    Location
    Pittsburgh, PA
    MS-Off Ver
    Excel 2013
    Posts
    200

    Re: Problem with VLookup and Index Match on mixed Text and Numeric codes

    Quote Originally Posted by sktneer View Post
    May be this........

    Please Login or Register  to view this content.
    See if this helps.
    Sktneer, good try but yours gives me a value error for values with Letters. Sorry to all, I'll reupload with a few more values that also show ones with letters. My fault, I made it too easy

  16. #16
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Problem with VLookup and Index Match on mixed Text and Numeric codes

    If the only issue with using Value(D2) or D2*1 is with the SKU's that have letters...
    Then try

    =INDEX(ZoneCategory,MATCH(IFERROR(D2*1,D2),sku,0))

  17. #17
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Problem with VLookup and Index Match on mixed Text and Numeric codes

    maybe...
    =INDEX(ZoneCategory,MATCH(iferror(D2*1,D2),sku,0))

    Jonmo beat me to it

  18. #18
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Problem with VLookup and Index Match on mixed Text and Numeric codes

    opps i saw it the wrong way around
    how about
    =INDEX(ZoneCategory,MATCH(IFERROR(D2+0,D2),sku,0))
    oops fdibbins beat me to it

  19. #19
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Problem with VLookup and Index Match on mixed Text and Numeric codes

    and JonMo beat us both, Martin

  20. #20
    Forum Contributor
    Join Date
    01-28-2010
    Location
    Pittsburgh, PA
    MS-Off Ver
    Excel 2013
    Posts
    200

    Re: Problem with VLookup and Index Match on mixed Text and Numeric codes

    Quote Originally Posted by Jonmo1 View Post
    If the only issue with using Value(D2) or D2*1 is with the SKU's that have letters...
    Then try

    =INDEX(ZoneCategory,MATCH(IFERROR(D2*1,D2),sku,0))

    We are close guys. While your code fixed what was given, I went back and applied it to the whole spreadsheet and still have some that refuse to be looked up. It's just so bizarre.

    Here's one more try at it - this is so frustrating...knowing exactly what I want to do and having a usual way to do it, but cannot accomplish it. Sorry if I am mucking something up.

    Inv Summary Check File - online.xlsx

  21. #21
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Problem with VLookup and Index Match on mixed Text and Numeric codes

    OK this works. It seems that some of your "numbers" in sheet2 are text as well...
    =INDEX(ZoneCategory,IFERROR(MATCH(D2,sku,0),MATCH(IFERROR(D2*1,D2),sku,0)))

    The only 1 that doesnt work now is this 1...
    sheet 1 D8 = 1000030
    sheet2 A11 = 1000300

  22. #22
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Problem with VLookup and Index Match on mixed Text and Numeric codes

    The formula in L8 is #N/A because the value in D8 (1000030) does not exist in the sku range (text value or not...it's not there)

    In L9 and down they are #N/A because the corresponding values in the sku range are "numbers stored as text"

    You have to have consistency in the lookup table. This is a MUST.
    In the SKU range, A2:A8 and A11 are all real numbers, but A12 and A13 are Numbers Stored as Text

  23. #23
    Forum Contributor
    Join Date
    01-28-2010
    Location
    Pittsburgh, PA
    MS-Off Ver
    Excel 2013
    Posts
    200

    Re: Problem with VLookup and Index Match on mixed Text and Numeric codes

    Quote Originally Posted by Jonmo1 View Post
    The formula in L8 is #N/A because the value in D8 (1000030) does not exist in the sku range (text value or not...it's not there)

    In L9 and down they are #N/A because the corresponding values in the sku range are "numbers stored as text"

    You have to have consistency in the lookup table. This is a MUST.
    In the SKU range, A2:A8 and A11 are all real numbers, but A12 and A13 are Numbers Stored as Text
    So for my own understanding - how should I store these two numbers to keep them consistent:
    00012
    00012T

    I thought storing as text would fix the issue, but it doesn't??

  24. #24
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Problem with VLookup and Index Match on mixed Text and Numeric codes

    Those are NOT 2 numbers.
    00012 is a number (because it does not contain any alpha characters)
    00012T is a Text string (because it contains an alpha character...T)

    Your Numbers need to be consistent, all of them stored as number.

  25. #25
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Problem with VLookup and Index Match on mixed Text and Numeric codes

    Did you see my suggestion in post # 21?

  26. #26
    Forum Contributor
    Join Date
    01-28-2010
    Location
    Pittsburgh, PA
    MS-Off Ver
    Excel 2013
    Posts
    200

    Re: Problem with VLookup and Index Match on mixed Text and Numeric codes

    Yes I did, but I noticed you had to make a complicated formula and thought that was because I did a crappy job at assigning the data values. Let me try yours, but looking for a rule of thumb going further so I can avoid complicated formulas. I didn't want you to think I was restricted to a certain data value - I was just trying everything!

  27. #27
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Problem with VLookup and Index Match on mixed Text and Numeric codes

    OH OK cool. It is, without doubt, always better to get your data in a consistent way. However, this is not always possible, so we sometimes have to create formulas that handle that inconsistency. My formula works if either side are text or numeric

  28. #28
    Forum Contributor
    Join Date
    01-28-2010
    Location
    Pittsburgh, PA
    MS-Off Ver
    Excel 2013
    Posts
    200

    Re: Problem with VLookup and Index Match on mixed Text and Numeric codes

    Quote Originally Posted by FDibbins View Post
    OK this works. It seems that some of your "numbers" in sheet2 are text as well...
    =INDEX(ZoneCategory,IFERROR(MATCH(D2,sku,0),MATCH(IFERROR(D2*1,D2),sku,0)))

    The only 1 that doesnt work now is this 1...
    sheet 1 D8 = 1000030
    sheet2 A11 = 1000300
    Looks like this did it. Who would have thought the solution would be so complicated?? Hopefully no other problems with this - rep to all! Thanks tremendously!

  29. #29
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Problem with VLookup and Index Match on mixed Text and Numeric codes

    Happy to help and thanks for the feedback

    Who would have thought the solution would be so complicated??
    That's what comes from inconsistent data

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Index, Match and Vlookup problem
    By trumpetman in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-06-2013, 05:15 PM
  2. Replies: 6
    Last Post: 05-28-2013, 05:08 PM
  3. vlookup? match? index? MULTIPLE criteria for vlookup search problem....
    By aborg88 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 02-11-2013, 09:56 AM
  4. Replies: 13
    Last Post: 12-13-2012, 11:44 AM
  5. Extract just numeric part of mixed text/number entry?
    By Heidi in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 06-01-2006, 02:35 PM

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