Vlookup with multiple occurances of a value

Hyp40

New Member
Joined
Apr 9, 2019
Messages
16
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

Board Regular
Joined
Aug 14, 2013
Messages
67
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
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,113
Office Version
365
Platform
Windows
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?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,113
Office Version
365
Platform
Windows
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

New Member
Joined
Apr 9, 2019
Messages
16
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

New Member
Joined
Apr 9, 2019
Messages
16
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,113
Office Version
365
Platform
Windows
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?
 
Last edited:

Hyp40

New Member
Joined
Apr 9, 2019
Messages
16
Ideally, below, so B13, C13, D13 and B14, C14, D14. Is that possible?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,113
Office Version
365
Platform
Windows
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
 

Forum statistics

Threads
1,077,636
Messages
5,335,387
Members
399,015
Latest member
emalabel

Some videos you may like

This Week's Hot Topics

Top