Vlookup with multiple occurances of a value

[Hyp40]

Hi. I have a master postcode list and have been using Vlookup to check if my postcode is in the master list and to bring back info from neighbouring cells in the master postcode list. This was working well until I found that my postcode could occur more than once in the master list (with different info in the neighbouring cells). How can I get each of these back rather than just the first one? Also, some cells in the master list contain a number of postcodes, separated by commas. The vlookup doesn't find any of these. The master list changes weekly, so it is impractical to separate out the cells with multiple postcodes in.

 

[pella88]

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

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

 

[Fluff]

A few questions
1) Which column holds the postcode?
2) Which columns do you want to return?
3) Where do you want that information put?

 

[Hyp40]

A few questions
1) Which column holds the postcode?
2) Which columns do you want to return?
3) Where do you want that information put?

1. A
2. A, B, C, D
3. A separate worksheet

 

[Fluff]

The answer to part 3 is less than helpful.

Whereabouts on the sheet do you want it put, and how should it handle the multiple entries?

 

[Hyp40]

Sorry, I misunderstood!
What I am trying to do is see if 'My Postcode' is in the master list (postcode col A, Number col B, Add1 col C, Add2 col D).
I would like to end up with a list of all 'My postcodes' that are in the master list along with their corresponding addresses. The vlookup worked well whilst there was only one instance of a postcode but now the master list has,
1. multiple instances of one postcode - so I need to display each of these with their address in my list, and
2. one cell may contain two or more postcodes separated with a comma

 

[Hyp40]

Thank you pella88. That is really interesting as I didn't realise I could do that.
I guess I didn't word my question well enough as it is not displaying the result quite as I need but will look at adapting. I really appreciate you help..

 

[Fluff]

Ok, you have a list of postcodes in col A & in cols B:D you want to return the details from the master sheet, if they exist.
If the postcode in(for instance) A12 exists 3 times in the master list, the first match will go into B12,C12 & D12, where do you want to put the other two sets of details?

Also what version of Excel are you using?

 

[Hyp40]

Ideally, below, so B13, C13, D13 and B14, C14, D14. Is that possible?

 

[Fluff]

That's possible, but are you looking up a list of postcodes, or just 1?
Because if it's a list then B13:D14 should be returning the values from A13:A14