Hi Hyp40,
I am not aware of a solution without VBA (possibly it can be done with CSE formulas and a bit of complication with Index/Match and similar), however, with VBA it is quite straightforward. Kudos to TrumpExcel for providing me with a bit of insight on how to do it.
1. Add the following code in new module in VBA
Code:
Function SingleCellLookup(LookupValue As String, LookupRange As Range, ColumnNumber As Integer) Dim i As Long
Dim Result As String
For i = 1 To LookupRange.Columns(1).Cells.Count
If LookupRange.Cells(i, 1) = LookupValue Then
Result = Result & " " & LookupRange.Cells(i, ColumnNumber) & ","
End If
Next i
SingleCellLookup = Left(Result, Len(Result) - 1)
End Function
2. Save excel as xlsm (not necessary, but you will have to add the formula every time in your excel if not saved as xlsm).
3. Use the formula the same as you would use VLOOKUP, except the last parameter. That is, the first parameter is the value you are trying to find (in your case postcode), second parameter is the range you are looking in for the value you want to return, where column 1 has postcodes. Third parameter is the number of column from which the result has to be returned (bear in mind that column counting starts from 1, where the column 1 are your postcodes).
Br
pella88