VLOOKUP 255 Character Limit - formula alternatives?

[chive90]

Hi All

Is there a way around the 255 VLOOKUP character limit by using an alternative formula? I've tried Index & Match but it also returns a VALUE error if I try and lookup the same Column as I was with VLOOKUP.

I'm using Office 2016.

Starting at C2, I'm wanting to lookup Column C Sheet1 against Column C Sheet2 and return Column H Sheet2.

Column C contains various strings that are in excess of 255 characters.

Thanks

 

[RoryA]

You should be able to do something like:

=LOOKUP(2,1/{Sheet2!$C$2:$C$1000=C2),Sheet2!$H$2:$H$1000)

 

[chive90]

You should be able to do something like:

=LOOKUP(2,1/{Sheet2!$C$2:$C$1000=C2),Sheet2!$H$2:$H$1000)

Thanks for the quick response. Does this also work across hundreds of thousands of rows without any limitations?

Presumably I can just change 1000 to 800000 for example?

Thanks

 

[RoryA]

Yes although performance will probably not be great if you have that much data.

 

[Joe4]

If you have that much data, and it is interrelated, when you are doing matching/lookups, there is a good chance that you are really dealing with a "Relational Database", in which case, you would probably get much better performance from a Relational Database program, like Microsoft Access, SQL, or Oracle.