+ Reply to Thread
Results 1 to 6 of 6

Lookup and return multiple values

  1. #1
    Registered User
    Join Date
    06-27-2012
    Location
    Dublin
    MS-Off Ver
    Excel 2003
    Posts
    28

    Lookup and return multiple values

    Hi Gurus,

    I am trying to populate values in sheet 2 column A to Column B in sheet 1. The data in lookup field is not unique therefore can't populate the correct customer number from sheet 2 to sheet 1. The lookup field in this case is OBA number.

    Since the data consist of thousands of lines, excel usually crashes if I try to apply any formula. Is it possible to have a macro to resolve this issue?

    I attach a sample file for reference.
    Attached Files Attached Files
    Last edited by kashaikh78; 12-04-2019 at 03:40 PM.

  2. #2
    Forum Expert Sambo kid's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    Office 365 ProPlus - work; 2012 for Mac at home
    Posts
    5,926

    Re: Lookup and return multiple values

    I know you want VBA but maybe you ought to reload the sample because using a simple index/match formula against the OBA number in sheet1 column A to try to match against the OBA number in sheet2 column E I get no matches. It might be helpful for someone testing VBA to have results to test it against.
    AND, I tested your OBA number in Sheet2 to see if there are any dups and there were none but you have dups of OBA numbers in sheet1. So what would the results look like?
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Registered User
    Join Date
    06-27-2012
    Location
    Dublin
    MS-Off Ver
    Excel 2003
    Posts
    28

    Re: Lookup and return multiple values

    Sorry. Since it was just a sample of the overall data, there were no duplicates. I now attached a revised spreadsheet with same OBA with different customers.

    Hope this can help.
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    288

    Re: Lookup and return multiple values

    Sheet1


    B4==IF(ISERROR(INDEX(Sheet2!$A$6:$A$3000,SMALL(IF(Sheet2!$E$6:$E$3000=Sheet1!$A4,ROW(Sheet2!$A$6:$A$3000)-ROW(Sheet2!$A$6)+1),COUNTIF(Sheet1!$A$4:Sheet1!A4,Sheet1!A4)))),"",INDEX(Sheet2!$A$6:$A$3000,SMALL(IF(Sheet2!$E$6:$E$3000=Sheet1!$A4,ROW(Sheet2!$A$6:$A$3000)-ROW(Sheet2!$A$6)+1),COUNTIF(Sheet1!$A$4:Sheet1!A4,Sheet1!A4))))

    control+shift+enter

    copy down

  5. #5
    Registered User
    Join Date
    06-27-2012
    Location
    Dublin
    MS-Off Ver
    Excel 2003
    Posts
    28

    Re: Lookup and return multiple values

    Great! it's worked.

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    23,777

    Re: Lookup and return multiple values

    kashaikh, it's clear that you are no longer using Excel 2003, as you attached an .xlsx. Please update your profile accordingly. Later Ecel versions may allow for simpler solutions.
    Glenn



+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1