255 Character limit on VLOOKUP












3















Using excel 2003, the formula:



=VLOOKUP(D1 ,A1:B135, 2)


fails if the length of D1 exceeds 255 characters (i.e. the list has some text longer then 255 characters, D1 has the same text value, and VLOOKUP returns #VALUE!).



MATCH seems to suffer from the same character limit.



I cannot find any official confirmation of these limits, for example here:



http://office.microsoft.com/en-us/excel-help/vlookup-HP005209335.aspx



or here:



http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP005199291.aspx?CTT=3



I know that excel has a 255 limit on the length of text used in formulae, but it suggests connate should work (it does not in this case, and I am not using strings in the formula, but referencing another cell).



Can somebody confirm that these limit exist (it is always possible I am doing something else wrong)?



More importantly, does anyone know of a way around them?



Thanks










share|improve this question


















  • 1





    Confirmed this behavior in both Excel 2003 and 2007.

    – Sux2Lose
    Feb 18 '11 at 14:11











  • Same issue (in Excel 2003)

    – wilson
    Feb 19 '11 at 20:46











  • Here is the solution which worked perfectly for me: stackoverflow.com/questions/13202473/…

    – user253260
    Sep 11 '13 at 14:45











  • Check below link - worked perfectly for me: stackoverflow.com/questions/13202473/…

    – user253260
    Sep 11 '13 at 14:45
















3















Using excel 2003, the formula:



=VLOOKUP(D1 ,A1:B135, 2)


fails if the length of D1 exceeds 255 characters (i.e. the list has some text longer then 255 characters, D1 has the same text value, and VLOOKUP returns #VALUE!).



MATCH seems to suffer from the same character limit.



I cannot find any official confirmation of these limits, for example here:



http://office.microsoft.com/en-us/excel-help/vlookup-HP005209335.aspx



or here:



http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP005199291.aspx?CTT=3



I know that excel has a 255 limit on the length of text used in formulae, but it suggests connate should work (it does not in this case, and I am not using strings in the formula, but referencing another cell).



Can somebody confirm that these limit exist (it is always possible I am doing something else wrong)?



More importantly, does anyone know of a way around them?



Thanks










share|improve this question


















  • 1





    Confirmed this behavior in both Excel 2003 and 2007.

    – Sux2Lose
    Feb 18 '11 at 14:11











  • Same issue (in Excel 2003)

    – wilson
    Feb 19 '11 at 20:46











  • Here is the solution which worked perfectly for me: stackoverflow.com/questions/13202473/…

    – user253260
    Sep 11 '13 at 14:45











  • Check below link - worked perfectly for me: stackoverflow.com/questions/13202473/…

    – user253260
    Sep 11 '13 at 14:45














3












3








3


3






Using excel 2003, the formula:



=VLOOKUP(D1 ,A1:B135, 2)


fails if the length of D1 exceeds 255 characters (i.e. the list has some text longer then 255 characters, D1 has the same text value, and VLOOKUP returns #VALUE!).



MATCH seems to suffer from the same character limit.



I cannot find any official confirmation of these limits, for example here:



http://office.microsoft.com/en-us/excel-help/vlookup-HP005209335.aspx



or here:



http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP005199291.aspx?CTT=3



I know that excel has a 255 limit on the length of text used in formulae, but it suggests connate should work (it does not in this case, and I am not using strings in the formula, but referencing another cell).



Can somebody confirm that these limit exist (it is always possible I am doing something else wrong)?



More importantly, does anyone know of a way around them?



Thanks










share|improve this question














Using excel 2003, the formula:



=VLOOKUP(D1 ,A1:B135, 2)


fails if the length of D1 exceeds 255 characters (i.e. the list has some text longer then 255 characters, D1 has the same text value, and VLOOKUP returns #VALUE!).



MATCH seems to suffer from the same character limit.



I cannot find any official confirmation of these limits, for example here:



http://office.microsoft.com/en-us/excel-help/vlookup-HP005209335.aspx



or here:



http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP005199291.aspx?CTT=3



I know that excel has a 255 limit on the length of text used in formulae, but it suggests connate should work (it does not in this case, and I am not using strings in the formula, but referencing another cell).



Can somebody confirm that these limit exist (it is always possible I am doing something else wrong)?



More importantly, does anyone know of a way around them?



Thanks







microsoft-excel microsoft-excel-2003 vlookup






share|improve this question













share|improve this question











share|improve this question




share|improve this question










asked Feb 18 '11 at 11:19









zodzod

116112




116112








  • 1





    Confirmed this behavior in both Excel 2003 and 2007.

    – Sux2Lose
    Feb 18 '11 at 14:11











  • Same issue (in Excel 2003)

    – wilson
    Feb 19 '11 at 20:46











  • Here is the solution which worked perfectly for me: stackoverflow.com/questions/13202473/…

    – user253260
    Sep 11 '13 at 14:45











  • Check below link - worked perfectly for me: stackoverflow.com/questions/13202473/…

    – user253260
    Sep 11 '13 at 14:45














  • 1





    Confirmed this behavior in both Excel 2003 and 2007.

    – Sux2Lose
    Feb 18 '11 at 14:11











  • Same issue (in Excel 2003)

    – wilson
    Feb 19 '11 at 20:46











  • Here is the solution which worked perfectly for me: stackoverflow.com/questions/13202473/…

    – user253260
    Sep 11 '13 at 14:45











  • Check below link - worked perfectly for me: stackoverflow.com/questions/13202473/…

    – user253260
    Sep 11 '13 at 14:45








1




1





Confirmed this behavior in both Excel 2003 and 2007.

– Sux2Lose
Feb 18 '11 at 14:11





Confirmed this behavior in both Excel 2003 and 2007.

– Sux2Lose
Feb 18 '11 at 14:11













Same issue (in Excel 2003)

– wilson
Feb 19 '11 at 20:46





Same issue (in Excel 2003)

– wilson
Feb 19 '11 at 20:46













Here is the solution which worked perfectly for me: stackoverflow.com/questions/13202473/…

– user253260
Sep 11 '13 at 14:45





Here is the solution which worked perfectly for me: stackoverflow.com/questions/13202473/…

– user253260
Sep 11 '13 at 14:45













Check below link - worked perfectly for me: stackoverflow.com/questions/13202473/…

– user253260
Sep 11 '13 at 14:45





Check below link - worked perfectly for me: stackoverflow.com/questions/13202473/…

– user253260
Sep 11 '13 at 14:45










3 Answers
3






active

oldest

votes


















3














You can always code your own VLOOKUP... but, as an alternative, you can compute a hash from your lookup value, and use it as a new lookup value.



Collisions may happen, but using a somewhat decent hash algorithm, like MD5, it shouldn't be a problem.



To create a hash function, you may use the ideas from https://stackoverflow.com/questions/125785/password-hash-function-for-excel-vba.






share|improve this answer





















  • 1





    There's also superuser.com/questions/550592/…, where the OP is wanting to hash specifically as they may want to use VLOOKUP later.

    – ernie
    May 31 '13 at 16:56





















2














Write your own lookup in VBA



Here's a start:



Function MyVL(v As Range, r As Range, os As Long) As Variant
Dim cl As Range

For Each cl In r.Columns(1).Cells
If v = cl Then
MyVL = cl.Offset(0, os - 1)
Exit Function
End If
Next
End Function





share|improve this answer

































    1














    I was searching for exact matches between 2 reports and ran into this. I concatenated the several different fields of one report and another report into one LONG string and then used Vlookup to identify if every field of 1000 records or so matched between the 2 reports. That's when I ran into the problem.



    Because it was a one-time thing and I could break it down into smaller chunks by concatenating less than 255 characters into 3 separate columns, I did that and wrote 3 separate lookups and compared the data in 3 chunks rather than all at once. The major drawback is that I had to re-sort by the lookup table (column) I wanted to run the lookup on for each of the 3 sub groups before running each lookup.



    Id call my solution perfectly acceptable for a onetime thing, but if I had to do it more than once, I'd look for something more efficient.






    share|improve this answer























      Your Answer








      StackExchange.ready(function() {
      var channelOptions = {
      tags: "".split(" "),
      id: "3"
      };
      initTagRenderer("".split(" "), "".split(" "), channelOptions);

      StackExchange.using("externalEditor", function() {
      // Have to fire editor after snippets, if snippets enabled
      if (StackExchange.settings.snippets.snippetsEnabled) {
      StackExchange.using("snippets", function() {
      createEditor();
      });
      }
      else {
      createEditor();
      }
      });

      function createEditor() {
      StackExchange.prepareEditor({
      heartbeatType: 'answer',
      autoActivateHeartbeat: false,
      convertImagesToLinks: true,
      noModals: true,
      showLowRepImageUploadWarning: true,
      reputationToPostImages: 10,
      bindNavPrevention: true,
      postfix: "",
      imageUploader: {
      brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
      contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
      allowUrls: true
      },
      onDemand: true,
      discardSelector: ".discard-answer"
      ,immediatelyShowMarkdownHelp:true
      });


      }
      });














      draft saved

      draft discarded


















      StackExchange.ready(
      function () {
      StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f247427%2f255-character-limit-on-vlookup%23new-answer', 'question_page');
      }
      );

      Post as a guest















      Required, but never shown

























      3 Answers
      3






      active

      oldest

      votes








      3 Answers
      3






      active

      oldest

      votes









      active

      oldest

      votes






      active

      oldest

      votes









      3














      You can always code your own VLOOKUP... but, as an alternative, you can compute a hash from your lookup value, and use it as a new lookup value.



      Collisions may happen, but using a somewhat decent hash algorithm, like MD5, it shouldn't be a problem.



      To create a hash function, you may use the ideas from https://stackoverflow.com/questions/125785/password-hash-function-for-excel-vba.






      share|improve this answer





















      • 1





        There's also superuser.com/questions/550592/…, where the OP is wanting to hash specifically as they may want to use VLOOKUP later.

        – ernie
        May 31 '13 at 16:56


















      3














      You can always code your own VLOOKUP... but, as an alternative, you can compute a hash from your lookup value, and use it as a new lookup value.



      Collisions may happen, but using a somewhat decent hash algorithm, like MD5, it shouldn't be a problem.



      To create a hash function, you may use the ideas from https://stackoverflow.com/questions/125785/password-hash-function-for-excel-vba.






      share|improve this answer





















      • 1





        There's also superuser.com/questions/550592/…, where the OP is wanting to hash specifically as they may want to use VLOOKUP later.

        – ernie
        May 31 '13 at 16:56
















      3












      3








      3







      You can always code your own VLOOKUP... but, as an alternative, you can compute a hash from your lookup value, and use it as a new lookup value.



      Collisions may happen, but using a somewhat decent hash algorithm, like MD5, it shouldn't be a problem.



      To create a hash function, you may use the ideas from https://stackoverflow.com/questions/125785/password-hash-function-for-excel-vba.






      share|improve this answer















      You can always code your own VLOOKUP... but, as an alternative, you can compute a hash from your lookup value, and use it as a new lookup value.



      Collisions may happen, but using a somewhat decent hash algorithm, like MD5, it shouldn't be a problem.



      To create a hash function, you may use the ideas from https://stackoverflow.com/questions/125785/password-hash-function-for-excel-vba.







      share|improve this answer














      share|improve this answer



      share|improve this answer








      edited May 23 '17 at 12:41









      Community

      1




      1










      answered Feb 18 '11 at 12:28









      wtaniguchiwtaniguchi

      465412




      465412








      • 1





        There's also superuser.com/questions/550592/…, where the OP is wanting to hash specifically as they may want to use VLOOKUP later.

        – ernie
        May 31 '13 at 16:56
















      • 1





        There's also superuser.com/questions/550592/…, where the OP is wanting to hash specifically as they may want to use VLOOKUP later.

        – ernie
        May 31 '13 at 16:56










      1




      1





      There's also superuser.com/questions/550592/…, where the OP is wanting to hash specifically as they may want to use VLOOKUP later.

      – ernie
      May 31 '13 at 16:56







      There's also superuser.com/questions/550592/…, where the OP is wanting to hash specifically as they may want to use VLOOKUP later.

      – ernie
      May 31 '13 at 16:56















      2














      Write your own lookup in VBA



      Here's a start:



      Function MyVL(v As Range, r As Range, os As Long) As Variant
      Dim cl As Range

      For Each cl In r.Columns(1).Cells
      If v = cl Then
      MyVL = cl.Offset(0, os - 1)
      Exit Function
      End If
      Next
      End Function





      share|improve this answer






























        2














        Write your own lookup in VBA



        Here's a start:



        Function MyVL(v As Range, r As Range, os As Long) As Variant
        Dim cl As Range

        For Each cl In r.Columns(1).Cells
        If v = cl Then
        MyVL = cl.Offset(0, os - 1)
        Exit Function
        End If
        Next
        End Function





        share|improve this answer




























          2












          2








          2







          Write your own lookup in VBA



          Here's a start:



          Function MyVL(v As Range, r As Range, os As Long) As Variant
          Dim cl As Range

          For Each cl In r.Columns(1).Cells
          If v = cl Then
          MyVL = cl.Offset(0, os - 1)
          Exit Function
          End If
          Next
          End Function





          share|improve this answer















          Write your own lookup in VBA



          Here's a start:



          Function MyVL(v As Range, r As Range, os As Long) As Variant
          Dim cl As Range

          For Each cl In r.Columns(1).Cells
          If v = cl Then
          MyVL = cl.Offset(0, os - 1)
          Exit Function
          End If
          Next
          End Function






          share|improve this answer














          share|improve this answer



          share|improve this answer








          edited Jan 31 at 2:21









          phuclv

          9,74363992




          9,74363992










          answered Feb 18 '11 at 12:15









          chris neilsenchris neilsen

          3,8651319




          3,8651319























              1














              I was searching for exact matches between 2 reports and ran into this. I concatenated the several different fields of one report and another report into one LONG string and then used Vlookup to identify if every field of 1000 records or so matched between the 2 reports. That's when I ran into the problem.



              Because it was a one-time thing and I could break it down into smaller chunks by concatenating less than 255 characters into 3 separate columns, I did that and wrote 3 separate lookups and compared the data in 3 chunks rather than all at once. The major drawback is that I had to re-sort by the lookup table (column) I wanted to run the lookup on for each of the 3 sub groups before running each lookup.



              Id call my solution perfectly acceptable for a onetime thing, but if I had to do it more than once, I'd look for something more efficient.






              share|improve this answer




























                1














                I was searching for exact matches between 2 reports and ran into this. I concatenated the several different fields of one report and another report into one LONG string and then used Vlookup to identify if every field of 1000 records or so matched between the 2 reports. That's when I ran into the problem.



                Because it was a one-time thing and I could break it down into smaller chunks by concatenating less than 255 characters into 3 separate columns, I did that and wrote 3 separate lookups and compared the data in 3 chunks rather than all at once. The major drawback is that I had to re-sort by the lookup table (column) I wanted to run the lookup on for each of the 3 sub groups before running each lookup.



                Id call my solution perfectly acceptable for a onetime thing, but if I had to do it more than once, I'd look for something more efficient.






                share|improve this answer


























                  1












                  1








                  1







                  I was searching for exact matches between 2 reports and ran into this. I concatenated the several different fields of one report and another report into one LONG string and then used Vlookup to identify if every field of 1000 records or so matched between the 2 reports. That's when I ran into the problem.



                  Because it was a one-time thing and I could break it down into smaller chunks by concatenating less than 255 characters into 3 separate columns, I did that and wrote 3 separate lookups and compared the data in 3 chunks rather than all at once. The major drawback is that I had to re-sort by the lookup table (column) I wanted to run the lookup on for each of the 3 sub groups before running each lookup.



                  Id call my solution perfectly acceptable for a onetime thing, but if I had to do it more than once, I'd look for something more efficient.






                  share|improve this answer













                  I was searching for exact matches between 2 reports and ran into this. I concatenated the several different fields of one report and another report into one LONG string and then used Vlookup to identify if every field of 1000 records or so matched between the 2 reports. That's when I ran into the problem.



                  Because it was a one-time thing and I could break it down into smaller chunks by concatenating less than 255 characters into 3 separate columns, I did that and wrote 3 separate lookups and compared the data in 3 chunks rather than all at once. The major drawback is that I had to re-sort by the lookup table (column) I wanted to run the lookup on for each of the 3 sub groups before running each lookup.



                  Id call my solution perfectly acceptable for a onetime thing, but if I had to do it more than once, I'd look for something more efficient.







                  share|improve this answer












                  share|improve this answer



                  share|improve this answer










                  answered May 31 '13 at 16:54









                  ElizabethElizabeth

                  111




                  111






























                      draft saved

                      draft discarded




















































                      Thanks for contributing an answer to Super User!


                      • Please be sure to answer the question. Provide details and share your research!

                      But avoid



                      • Asking for help, clarification, or responding to other answers.

                      • Making statements based on opinion; back them up with references or personal experience.


                      To learn more, see our tips on writing great answers.




                      draft saved


                      draft discarded














                      StackExchange.ready(
                      function () {
                      StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f247427%2f255-character-limit-on-vlookup%23new-answer', 'question_page');
                      }
                      );

                      Post as a guest















                      Required, but never shown





















































                      Required, but never shown














                      Required, but never shown












                      Required, but never shown







                      Required, but never shown

































                      Required, but never shown














                      Required, but never shown












                      Required, but never shown







                      Required, but never shown







                      Popular posts from this blog

                      How to make a Squid Proxy server?

                      Is this a new Fibonacci Identity?

                      19世紀