Character Limit on VLOOKUP & MATCH

robertgrumbles

New Member
Joined
Sep 27, 2011
Messages
35
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!!!
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,337
Members
448,568
Latest member
Honeymonster123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top