Substitute for VLOOKUP when Looking Up Large String

Substitute for VLOOKUP when Looking Up Large String

March 28, 2017

VLOOKUP is a great workhorse for Excel users.   However the formula used to find cross referenced entries in an array for a particular value has a serious limitation.    If the value you're searching for is greater than 256 characters, the formula will not function and an '# VALUE!' will be returned.   See the below example.   I am trying to determine if the data in columns C to X on row 26 are matched exactly in the array at C1:X23.   In column B, I have combined the values in columns C to X using the macro described in the Tip of the Night for July 28, 2015.   


When  a VLOOKUP formula is entered in A26, searching for the combined value in B26, the formula fails. 


The solution to this problem is to use an INDEX MATCH formula like this:




B26 is the value we're looking for, $B1:$B23, is where we search for it, and $A$1:$A$23 is the range from which a cross referenced value is pulled.   So in this example:


. . . the control number for the row containing the search for data, '13' listed in column A is pulled by the array formula.   As always when using an array formula be sure to press SHIFT + CTRL when entering it so it is surrounded by brackets. 


Thanks to Barry Houdini for posting this formula on stackoverflow.,   



Please reload

Contact Me With Your Litigation Support Questions:

  • Twitter Long Shadow

© 2015 by Sean O'Shea . Proudly created with