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:

Â

=INDEX(\$A1:\$A\$23,MATCH(TRUE,INDEX(\$B\$1:\$B\$23=B26,0),0))

Â

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., Â Â

Â

Â