Workaround for Match 255 character limit

[dslhs]

Hello,

The following formula is working like a dream, except some of the Match Lookups (B4 below) have more than 255 characters, meaning it's returning #VALUE!

=INDEX('[DOCUMENT.xlsm]EHCP Objectives'!$A$2:$D$400,MATCH(B4,'[DOCUMENT.xlsm]EHCP Objectives'!$B$2:$B$400,0),1)

Every workaround I've found online doesn't seem to work. Anyone have any ideas? It would be very much appreciated.

Thank you

 

[RoryA]

Try this:

=LOOKUP(2,1/('[DOCUMENT.xlsm]EHCP Objectives'!$B$2:$B$400=B4),'[DOCUMENT.xlsm]EHCP Objectives'!$A$2:$A$400)

 

[dslhs]

Hero

Thanks!