Page 1 of 3 123 LastLast
Results 1 to 10 of 24

Thread: Vlookup with multiple occurances of a value

  1. #1
    New Member
    Join Date
    Apr 2019
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Vlookup with multiple occurances of a value

    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.

  2. #2
    Board Regular
    Join Date
    Aug 2013
    Posts
    67
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Vlookup with multiple occurances of a value

    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 by pella88; Sep 21st, 2019 at 07:21 AM.

  3. #3
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,120
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Vlookup with multiple occurances of a value

    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?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  4. #4
    New Member
    Join Date
    Apr 2019
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Vlookup with multiple occurances of a value

    Quote Originally Posted by Fluff View Post
    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

  5. #5
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,120
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Vlookup with multiple occurances of a value

    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?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  6. #6
    New Member
    Join Date
    Apr 2019
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Vlookup with multiple occurances of a value

    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

  7. #7
    New Member
    Join Date
    Apr 2019
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Vlookup with multiple occurances of a value

    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..

  8. #8
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,120
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Vlookup with multiple occurances of a value

    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 by Fluff; Sep 21st, 2019 at 11:40 AM.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  9. #9
    New Member
    Join Date
    Apr 2019
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Vlookup with multiple occurances of a value

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

  10. #10
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,120
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Vlookup with multiple occurances of a value

    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
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •