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,545
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,545
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,545
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,545
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,078,467
Messages
5,340,498
Members
399,379
Latest member
Ashrafkamal

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top