How to do a Vlookup for part of a value

[phipe]

Hello,

Here is my problem - I have a list with all members of our website - 40 000 entries and their postcodes. Postcodes are in UK so typical format is for example SE1 5TW
I also have another list with postcode sectors provided by a client. So if a postcode is SE1 5TW, the sector will be fust SE1 5

The client wants to know how many members we have in this sectors, so:

If I have A1:A40000 all the postcodes and
B1:B1650 all the sectors how can I find how many (members)postcodes we have for each sector ? Note that postcodes may not be unique.

I may also need to match results to other data later, so actual postcodes rather than just a number (count) is what I`m after.

I tried Vlookup(B1&"*", A:A, 1, False) but I realised that I actually need Vlookup(A1,B1&"*":B1650&"*", 1, False) however I believe there is no such formula.

Any help will be really appreciated !

Thanks in advance !

Filip

 

[Airfix9]

If the full postcode is in column A, and you are looking at a list of sectors, you could use the Find command within the VLOOKUP:

=VLOOKUP(LEFT(A1,FIND(" ",A1)+1)),A:A,1,0)

If you are looking in a list of postcodes, not sectors, look up LEFT(A1,FIND(" ",A1)+1))&"*"