+ Reply to Thread
Results 1 to 24 of 24

Multiple lookup results

  1. #1
    Forum Contributor
    Join Date
    05-18-2017
    Location
    London
    MS-Off Ver
    2013
    Posts
    126

    Multiple lookup results

    Hi all,

    I m Looking to write a formula for below requirement. But not easy.
    In the result column i need all the countries whr employee is present.
    Country Emp Number Result
    DE 1 DE GB
    GB 1 GB DE

    Anyone has an idea what can b done plase?

    BR,

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,323

    Re: Multiple lookup results

    Based on the very scant data provided, sorry, but I have no idea! What would the result be for that sample?

    Please provide a sample workbook with a more extensive and realistic set of data for us to work with, along with manually entered results as you expect to see them.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Contributor
    Join Date
    05-18-2017
    Location
    London
    MS-Off Ver
    2013
    Posts
    126

    Re: Multiple lookup results

    Thank you!

    I m posting this from mobile, so i have no possiblity to attach files. I will seperate the colum with ‘|’ symbol. Hopefully helps..

    I m Looking to write a formula for below requirement. But not easy.

    Country | Emp Number | Result
    DE | 1 | DE GB
    GB | 1 | GB DE

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,323

    Re: Multiple lookup results

    No, it doesn't help because you have not answered my question!

    Explain the expected results. If you can't, maybe wait until you can upload a better sample?

  5. #5
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Multiple lookup results

    Enter this code into a standard vba module.
    Please Login or Register  to view this content.
    Then enter this array formula for the first result, copy down as needed.

    =TRIM(aconcat(IF($B$2:$B$3=B2,$A$2:$A$3&" ","")))

    Must be confirmed as an array using Shift Ctrl Enter.

  6. #6
    Forum Contributor
    Join Date
    05-18-2017
    Location
    London
    MS-Off Ver
    2013
    Posts
    126

    Re: Multiple lookup results

    File is attached. Many thanks!
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    05-18-2017
    Location
    London
    MS-Off Ver
    2013
    Posts
    126

    Re: Multiple lookup results

    Thank you, very kind of you...
    how ever, I have never done VB before.. is it possible with formula itself?

  8. #8
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Multiple lookup results

    You cannot get the results in a single cell without vba.

    However, this array formula will achieve the same result over multiple columns.

    Enter it into C2 of your test file and confirm the array with Shift Ctrl Enter.

    =IF(B2="","",IFERROR(INDEX($B:$B,SMALL(IF($A$2:$A$11=$A2,ROW($A$2:$A$11)),COLUMNS($C2:C2))),""))

    Then fill down and right.

  9. #9
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Cool Re: Multiple lookup results

    You can do that with PowerQuery

    Power Query for

    Note: It doesn't work without PowerQuery

  10. #10
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Multiple lookup results

    @Sandy

    It would be helpful to all if you would provide some description of how you create the power query rather than just a completed workbook.

    How would you expect the OP to apply that to a different workbook with no explanation ?

  11. #11
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Multiple lookup results

    @ jason.b75
    if OP will be interested it will be explained. if not, all expalnation doesn't make any sense. to see how it works - see note.

  12. #12
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 2019
    Posts
    2,360

    Re: Multiple lookup results

    Try this brute force method (not recommended for big data just for 10 rows, put his on C2 and copied down
    =IFERROR(IF(INDEX($B$2:$B$11,AGGREGATE(15,6,(ROW($B$2:$B$11)-MIN(ROW($B$2:$B$11))+1)/($A$2:$A$11=A2),1))=0,"",INDEX($B$2:$B$11,AGGREGATE(15,6,(ROW($B$2:$B$11)-MIN(ROW($B$2:$B$11))+1)/($A$2:$A$11=A2),1))),"")&" "&IFERROR(IF(INDEX($B$2:$B$11,AGGREGATE(15,6,(ROW($B$2:$B$11)-MIN(ROW($B$2:$B$11))+1)/($A$2:$A$11=A2),2))=0,"",INDEX($B$2:$B$11,AGGREGATE(15,6,(ROW($B$2:$B$11)-MIN(ROW($B$2:$B$11))+1)/($A$2:$A$11=A2),2))),"")&" "&IFERROR(IF(INDEX($B$2:$B$11,AGGREGATE(15,6,(ROW($B$2:$B$11)-MIN(ROW($B$2:$B$11))+1)/($A$2:$A$11=A2),3))=0,"",INDEX($B$2:$B$11,AGGREGATE(15,6,(ROW($B$2:$B$11)-MIN(ROW($B$2:$B$11))+1)/($A$2:$A$11=A2),3))),"")&" "&IFERROR(IF(INDEX($B$2:$B$11,AGGREGATE(15,6,(ROW($B$2:$B$11)-MIN(ROW($B$2:$B$11))+1)/($A$2:$A$11=A2),4))=0,"",INDEX($B$2:$B$11,AGGREGATE(15,6,(ROW($B$2:$B$11)-MIN(ROW($B$2:$B$11))+1)/($A$2:$A$11=A2),4))),"")&" "&IFERROR(IF(INDEX($B$2:$B$11,AGGREGATE(15,6,(ROW($B$2:$B$11)-MIN(ROW($B$2:$B$11))+1)/($A$2:$A$11=A2),5))=0,"",INDEX($B$2:$B$11,AGGREGATE(15,6,(ROW($B$2:$B$11)-MIN(ROW($B$2:$B$11))+1)/($A$2:$A$11=A2),5))),"")&" "&IFERROR(IF(INDEX($B$2:$B$11,AGGREGATE(15,6,(ROW($B$2:$B$11)-MIN(ROW($B$2:$B$11))+1)/($A$2:$A$11=A2),6))=0,"",INDEX($B$2:$B$11,AGGREGATE(15,6,(ROW($B$2:$B$11)-MIN(ROW($B$2:$B$11))+1)/($A$2:$A$11=A2),6))),"")&" "&IFERROR(IF(INDEX($B$2:$B$11,AGGREGATE(15,6,(ROW($B$2:$B$11)-MIN(ROW($B$2:$B$11))+1)/($A$2:$A$11=A2),7))=0,"",INDEX($B$2:$B$11,AGGREGATE(15,6,(ROW($B$2:$B$11)-MIN(ROW($B$2:$B$11))+1)/($A$2:$A$11=A2),7))),"")&" "&IFERROR(IF(INDEX($B$2:$B$11,AGGREGATE(15,6,(ROW($B$2:$B$11)-MIN(ROW($B$2:$B$11))+1)/($A$2:$A$11=A2),8))=0,"",INDEX($B$2:$B$11,AGGREGATE(15,6,(ROW($B$2:$B$11)-MIN(ROW($B$2:$B$11))+1)/($A$2:$A$11=A2),8))),"")&" "&IFERROR(IF(INDEX($B$2:$B$11,AGGREGATE(15,6,(ROW($B$2:$B$11)-MIN(ROW($B$2:$B$11))+1)/($A$2:$A$11=A2),9))=0,"",INDEX($B$2:$B$11,AGGREGATE(15,6,(ROW($B$2:$B$11)-MIN(ROW($B$2:$B$11))+1)/($A$2:$A$11=A2),9))),"")&" "&IFERROR(IF(INDEX($B$2:$B$11,AGGREGATE(15,6,(ROW($B$2:$B$11)-MIN(ROW($B$2:$B$11))+1)/($A$2:$A$11=A2),10))=0,"",INDEX($B$2:$B$11,AGGREGATE(15,6,(ROW($B$2:$B$11)-MIN(ROW($B$2:$B$11))+1)/($A$2:$A$11=A2),10))),"")
    Attached Files Attached Files

  13. #13
    Forum Contributor
    Join Date
    05-18-2017
    Location
    London
    MS-Off Ver
    2013
    Posts
    126
    Thank you ,

    My data is big, 7000 rows. Employee record can be in upto 5 countries.
    I m looking for excel formula that can help me.

    Best regards,
    Dhinesh.

  14. #14
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Multiple lookup results

    can be down with helper column is it OK?
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  15. #15
    Forum Contributor
    Join Date
    05-18-2017
    Location
    London
    MS-Off Ver
    2013
    Posts
    126

    Re: Multiple lookup results

    yes.. please..

  16. #16
    Forum Contributor
    Join Date
    05-18-2017
    Location
    London
    MS-Off Ver
    2013
    Posts
    126

    Re: Multiple lookup results

    Hello Jason, above formula is not giving desired results. sorry...

  17. #17
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,323

    Re: Multiple lookup results

    Did you enter it normally or with CTRL+SHIFT+ENTER as instructed by Jason?

  18. #18
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Multiple lookup results

    C2=TRIM(IFERROR(LOOKUP(2,1/(A$1:A1=A2),C$1:C1),"")&" "&B2)
    D2=LOOKUP(2,1/(A$1:A$11=A2),C$1:C$11)
    Try this and copy towards down
    see the attached file
    Attached Files Attached Files

  19. #19
    Forum Contributor
    Join Date
    05-18-2017
    Location
    London
    MS-Off Ver
    2013
    Posts
    126

    Re: Multiple lookup results

    Hello Samba, thank you...

    does it work if employee is in 5 countries? I see that it has worked for 3 countries.
    looks like A1=A2, means, the names should be in the right order? which is not the case with the data I have. even if I sort it I m unsure if it helps...

    would you be kind and answer the above please?

    BR,
    D.

  20. #20
    Forum Contributor
    Join Date
    05-18-2017
    Location
    London
    MS-Off Ver
    2013
    Posts
    126

    Re: Multiple lookup results

    yes, I did, it only gives one country.

  21. #21
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Multiple lookup results

    Read the last line of post #8 again

    Then fill down and right.
    If it only returns 1 country, then you only filled down, you will get the rest as you fill right, one country per column.

    However, Samba's 2 column method in post #18 will give the results you asked for with less effort entering the formulas.

  22. #22
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Multiple lookup results

    It works for N number of countries, you need to change the below range as per your actual range (change 11 as maximum row number of your range)
    A$1:A$11 & C$1:C$11

  23. #23
    Registered User
    Join Date
    09-24-2020
    Location
    Athens
    MS-Off Ver
    2013
    Posts
    13

    Re: Multiple lookup results

    Hi to everyone!

    I have created an index match function that return a value based on several criteria. My problem is that with these criteria i have multiple possible outcomes which i am interested in finding
    (and extract in a table?). My way always return a single value. Any ideas?
    Attached you may find my workbook.
    Attached Files Attached Files

  24. #24
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,530

    Re: Multiple lookup results

    Quote Originally Posted by jiminho13 View Post
    Hi to everyone!
    You are posting an unrelated question to thread that is over two years old


    Administrative Note:

    Welcome to the forum.

    We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

    Please see Forum Rule #4 about hijacking and start a new thread for your query.

    If you are not familiar with how to start a new thread see the FAQ: How to start a new thread
    Jeff
    | | |·| |·| |·| |·| | |:| | |·| |·|
    Read the rules
    Use code tags to [code]enclose your code![/code]

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Lookup to match multiple criteria and return multiple results excluding duplicates.
    By kris3012 in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 01-13-2018, 11:06 AM
  2. Replies: 4
    Last Post: 05-19-2015, 08:42 PM
  3. Replies: 1
    Last Post: 09-04-2014, 12:14 PM
  4. [SOLVED] Is it possible to lookup multiple values and return multiple rows of results?
    By justin11 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-20-2013, 12:02 PM
  5. [SOLVED] Sumifs with multiple lookups with multiple results to lookup other values
    By malisesaucier in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-10-2013, 01:13 AM
  6. Replies: 11
    Last Post: 02-07-2013, 02:22 AM
  7. Lookup multiple & non duplicated results for multiple matching criteria
    By melvil007 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-19-2012, 01:22 AM

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