VLOOKUP #value error

[Texarina]

In the Vlookup function, I am trying to use a long text string as my lookup
value. For example, my Vlookup function reads:

=VLOOKUP(B3,'List of All Scenarios'!$B$3:$H$1000,2,FALSE)

where the lookup value is a text string such as:

The scenario: The quick brown fox, although he does not normally put himself
in danger (based on careful analysis of the situation) jumps over the lazy
dog, and his owner pays him $10,000.01 dollars each time, and if he clears
the dog 6 or more times within this period he will earn a bonus of $8,000.00
dollars. (By DOG BREED). [ In Geographic Area ]


Obviously I've changed the text to protect confidential information, but the
punctuation and use of characters is the same. In some cases the text may be
more than 255 characters. I have tried using the trim and clean functions on
the text string to remove all nonprinting characters and leading and trailing
spaces in both the lookup table and the lookup value itself, but no matter
what I do the function returns a #value error. I've never had any trouble
with any vlookup in the past. (Note: the text above was not trimmed or
cleaned, although it was altered.)

Is there a way to make this work?

Thank you,

 

[Jan Karel Pieterse]

Hi Texarina,

Obviously I've changed the text to protect confidential information, but the
punctuation and use of characters is the same. In some cases the text may be
more than 255 characters.

I expect the length of the text is what is causing the problem.
You might use an array function like:

=INDEX(B$1:B$20,MIN(IF($A$1:$A$20=E1,ROW($A$1:$A$20),1E+299)))

(enter formula using control+shift+enter)

Where you want to have the value from column B for a match between cell F1 and
column A.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com