Excel formula help needed please

Copper Contributor


I am new to Excel and have been asked to create a formula to return the post code region the order is going to. 

The city postcode is in column H on "data" worksheet. 

On "Postcode Info" tab there are 5 Header columns A2-F2 e.g. West Country (Swindon), North Warrington etc and then below those header sections there are postcodes.

I need to have a formula that looks at the postcode (data tab) and then determines what region is should come under (postcode data tab) and return the header section e.g. rh10 9ts will return the value "South Hyde".  I have tried using the left formula =LEFT(H2,4) to return the postcode but the postcode can range from 1 letter to 2 letters and a digit.  Can anyone please help me as I really cant work it out.  Thankspicture 1 help needed 24092023.pngpicture 2 help needed 24092023.png

5 Replies


Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

@Hans Vogelaar im sorry if im doing something wrong.  It wont allow me to add any attachment all it is giving is a url link etc.  It will allow me to add photos so added the picture to show what its bringing up.  Sorry.problem 24092023.png  




I created a more convenient lookup range; this allowed me to create an INDEX/MATCH/SEARCH formula.

See the attached version.

best response confirmed by Rachel1950 (Copper Contributor)
thank you so much :) :) this has worked :)