Return a code based off post code

[jasonoakley]

I have a list of codes that relate to post codes, e.g. if a post code contains B2, B3, B10 etc then the code is B 1-1 if it contains B1, WS10, WV4 etc then the code is B 1-2 and so.

I'm trying to get it so when I type in the post code into the first cell, the next cell automatically populates with the code, I've tried lookup and if formulas but I can't seem to get it to allow me to do multiple arguments and return different values.

 

[Micron]

One way would be to use the sheet change event:

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo errHandler
Application.EnableEvents = False
Select Case Ucase(Target)
    Case "B2", "B3", "B10"
        Target.Offset(0, 1) = "B 1-1"
    Case "B1", "WS10", "WV4"
        Target.Offset(0, 1) = "B 1-2"
    'add other cases
End Select

exitHere:
Application.EnableEvents = True
Exit Sub

errHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume exitHere
        
End Sub

Ucase should make it work even if you type in b1 instead of B1.

 

[DanteAmor]

Try this.
In a table you put the codes and post codes.
Then you use the vlookup function

varios 07ene2024.xlsm
	A	B	C	D	E	F	G
1						Codes	Post Code
2	B10	B 1-1				B2	B 1-1
3	WS10	B 1-2				B3	B 1-1
4	B2	B 1-1				B10	B 1-1
5	b1	B 1-2				B1	B 1-2
6						WS10	B 1-2
7						WV4	B 1-2
Hoja6

Cell Formulas
Range	Formula
B2:B5	B2	=VLOOKUP(A2,F:G,2,0)