Character Limit on VLOOKUP & MATCH

[robertgrumbles]

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!!!

 

[Jonmo1]

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