Excel how do I lookup cells with more than 250 characters?

Question

Excel how do I lookup cells with more than 250 characters?

Laura McQuillan 0

I wish to look up cells in sheet-1 in a list in sheet-2. The cells contain text with more then 250 characters.

I get the error #VALUE.

How do I overcome this?

0 comments

1 answer

Your answer

Answer 1

Hi

This is a known VLOOKUP limitation. If the lookup text is longer than 255 characters, VLOOKUP can return #VALUE!. Microsoft recommends using INDEX + MATCH instead.

For example, if Sheet1!A2 contains the long text, Sheet2 column A contains the text to search, and column B contains the value you want returned, try:

=INDEX(Sheet2!$B$2:$B$1000,MATCH(TRUE,Sheet2!$A$2:$A$1000=Sheet1!A2,0))

With Microsoft 365, just press Enter. With older Excel versions, press Ctrl+Shift+Enter.

Microsoft’s explanation is here: https://support.microsoft.com/en-us/excel/how-to-correct-a-value-error-in-the-vlookup-function

If you still get #N/A, make sure the two long text strings are exactly identical, including spaces or hidden characters.