Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
LIVE
SOLVED

Help with Data Columns

Copper Contributor

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.

6 Replies
best response confirmed by dipeshdhanak (Copper Contributor)
Solution

@dipeshdhanak 

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

@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.

help with data columns.png

Thanks a lot think that saved me a few days.
Thanks a lot.
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?

@dipeshdhanak 

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

1 best response

Accepted Solutions
best response confirmed by dipeshdhanak (Copper Contributor)
Solution

@dipeshdhanak 

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

View solution in original post