lookup function fix

Hi All,

Hopefully a quick one to solve here:

To give you an idea of what I'm doing, I'm wanting to match a postcode with the first part of a postcode (postcode identifier) so I can assign a region code to it (find attached a sample to see what I'm talking about).

I've been given the below formula which works a treat, however when I expand the table size (see bold, the real set has about 10,000 rows) as below:

=LOOKUP(2,1/COUNTIF(C2,SUBSTITUTE('Region codes'!$A$2:$A$24," ","")&"*"),'Region codes'!$A$2:$A$24)

So if I change to this:

=LOOKUP(2,1/COUNTIF(C2,SUBSTITUTE('Region codes'!$A$2:$A$10000," ","")&"*"),'Region codes'!$A$2:$A$10000)

It returns a 0 result, do you know why this is and how to fix?

Thank you

Here's the attachment

Hi thanks for this, it still appears to be returning a 0 unfortunately.

This works a treat, I'll try and understand what you did to fix that

Thanks for your help