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.
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
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:
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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..
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,486
Messages
6,113,932
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top