Excel formula help needed please

[Rachel1950]

Hi,

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

[#2]

@Rachel1950 

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?

[#3]

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

[#4]

@Hans Vogelaar 

ive just managed to add the link :) is https://1drv.ms/x/s!AvgGqr39r8WulWCilrG4FTaaQBLj?e=48Lek9 

[#5]

@Rachel1950 

Thanks!

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

See the attached version.

[#6 · Accepted solution]

thank you so much :) :) this has worked :)