Results 1 to 2 of 2

Character Limit on VLOOKUP & MATCH

This is a discussion on Character Limit on VLOOKUP & MATCH within the Excel Questions forums, part of the Question Forums category; So I am trying to use a Vlookup on a cell that has a large amount of characters. Basically, the ...

  1. #1
    New Member
    Join Date
    Sep 2011
    Posts
    35

    Default Character Limit on VLOOKUP & MATCH

    So I am trying to use a Vlookup on a cell that has a large amount of characters. Basically, the cell is a multiple choice question, and I need the answer returned to the cell next to it. Right now I am using match and vlookup functions to accomplish this, but when a large question is pulled, the functions don't work. I'm guessing that I am exceding some kind of character limit. Is there a workaround for this? I am thinking I should use the left function to match up the first 50 or so characters and look those up. Here is the functions I am using right now. I need them modified so that they lookup only the first 50 characters. Thank you so much in advance!!!

    =OFFSET(Database!$C$2,MATCH(Front,DynamicList,0)-1,0)
    =VLOOKUP(DropDown,Database!B:E,4,false)

    "Front", "DynamicList", and "DropDown" are all named ranges.

    Thank you again!!!

  2. #2
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    31,878

    Default Re: Character Limit on VLOOKUP & MATCH

    These will be array entered formulas with CTRL + SHIFT + ENTER

    =VLOOKUP(DropDown,Database!B:E,4,false)
    =INDEX(Database!E$1:E$1000,MATCH(LEFT(DropDown,50),LEFT(Database!$B$1:$B$1000,50),0))

    =OFFSET(Database!$C$2,MATCH(Front,DynamicList,0)-1,0)
    =OFFSET(Database!$C$2,MATCH(LEFT(Front,50),LEFT(DynamicList,50),0)-1,0)


    IMPORTANT.
    You can't use entire column refs like E:E in an array formula.
    You must specify row #s like E1:E1000
    Want better/faster responses to your questions?
    Use Excel Jeanie to post samples of your sheet.

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

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
  •  


DMCA.com