more than 255 character look-up

[nathan82]

Hi,

I have 2 lists of 50,000 items each.
Each item in theses lists are above 255 characters long.

When I try to Vlookup one item in the other list, i get #VALUE error becuase the item is more than 255 characters long.

I tried writing a macro with 2 nested loops to search each item in list-1 within list 2. This is slow. Taking nearly 2 hours.

Any alternate ideas to speed this up?

Many thanks...

 

[RoryA]

I don't know if this will be any faster, but you can use this syntax:

=INDEX(ResultColumn, MATCH(TRUE, INDEX(MatchColumn=lookup value, 0), 0))

and it should work with longer text.