Help with Data Columns

[dipeshdhanak]

I have a column of Data with a partial postcode, and each one is assigned to a Sales officer.  so e.g., CV, B, DY, WV are all assigned to Joe Bloggs for example.  In another column I have full postcodes, and I have to put the correct sales agent name next to the full postcode.  Is there a quick way of doing this?  

So far the way I have found is using Vlookup, but in the column with the full postcode, I have to delete and make it into a partial postcode so I can use exact lookup otherwise it won't give me the correct sales agent.  

Thank you in advance.

[#2 · Accepted solution]

@dipeshdhanak 

You haven't told us what the full postcodes look like, so I made some up. See the attached demo.

[#3]

@dipeshdhanak 

=INDEX($B$2:$B$5,MATCH(TRUE,ISNUMBER(SEARCH($A$2:$A$5,E2)),0))

An alternative could be this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel for the web or Excel 2021.

[#4]

Thanks a lot think that saved me a few days.

[#5]

Thanks a lot.

[#6]

Actually upon checking, for some reason on a few of them, it has returned the wrong sales agent for some reason. So e.g. if im looking for BN8 6QB, and BN is Mrs Joy, its bringing up Mr Patel instead. BB is assigned to Mr Patel, so maybe the formula is getting confused when the full postcode has repeat letters in there?

[#7]

@dipeshdhanak 

Can you provide a few examples of what the full postcodes look like?