+ Reply to Thread
Results 1 to 11 of 11

Lookup same value but return different results

  1. #1
    Registered User
    Join Date
    04-29-2021
    Location
    Philiipines
    MS-Off Ver
    2020
    Posts
    2

    Unhappy Lookup same value but return different results

    Hello, I am trying to return values for a column that contains the same name for some. But the look up always returns the first value it finds.
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by excelnoob57; 05-05-2021 at 08:29 AM.

  2. #2
    Forum Contributor
    Join Date
    02-09-2021
    Location
    New York
    MS-Off Ver
    Office 2019
    Posts
    140

    Re: Lookup same value but return different results

    Please try,



    HTML Code: 
    =INDEX($B$4:$B$8,SMALL(IF($A$4:$A$8=D4,ROW($A$4:$A$8)-ROW(INDEX($A$4:$A$8,1,1))+1),1))
    Important this is an array: Please use Shift+Crtl+Enter.


    Hope this helps
    Last edited by D13L; 05-04-2021 at 05:35 AM. Reason: MISUNDERSTOOD REQUEST

  3. #3
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    7,645

    Re: Lookup same value but return different results

    With name is in A4:A8, D4:D8

    Found Age In E8:

    =IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW($A$4:$A$8)/($A$4:$A$8=D4),COUNTIF($D$4:$D$8,D4))),"")

  4. #4
    Forum Guru Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    11,960

    Re: Lookup same value but return different results

    Welcome to the forum

    Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  5. #5
    Registered User
    Join Date
    04-29-2021
    Location
    Philiipines
    MS-Off Ver
    2020
    Posts
    2

    Re: Lookup same value but return different results

    I have included the excel file because I seem to not get the desired result from the formulas above. Thanks everyone

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    17,929

    Re: Lookup same value but return different results

    With name in A4:A8, D4:D8


    =IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($A$4:$A$8)/($A$4:$A$8=$D4),COUNTIF($D$4:$D4,$D4))),"")

    =IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW($A$4:$A$8)/($A$4:$A$8=$D4),COUNTIF($D$4:$D4,$D4))),"")

    Minor correction to Bebo post #3
    Attached Files Attached Files
    Last edited by JohnTopley; 05-04-2021 at 03:36 PM.

  7. #7
    Forum Contributor
    Join Date
    02-06-2013
    Location
    Maryland
    MS-Off Ver
    Excel 2010
    Posts
    812

    Re: Lookup same value but return different results

    excelnoob57: See solution in column M2:M6. If this is the solution you are looking for, mark thread as solved.
    Attached Files Attached Files

  8. #8
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,875

    Re: Lookup same value but return different results

    @ bjnockle

    Sorry for off-topic interjection:

    Although there is no official rule regarding this behavior, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

    I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.

    Thanks again for all your hard work here!
    Dave

  9. #9
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,875

    Re: Lookup same value but return different results

    Similarly in cell E4:
    Sorts and returns duplicated ages directly rather than row numbers.

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by FlameRetired; 05-04-2021 at 11:42 PM.

  10. #10
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2010
    Posts
    5,827

    Re: Lookup same value but return different results

    In E4 then copy down

    =IFERROR(INDEX($B$4:$B$8,AGGREGATE(15,6,ROW($B$4:$B$8)/($A$4:$A$8=$D4),COUNTIF($D$4:$D4,$D4))-ROW($D$3)),"")
    Attached Files Attached Files
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  11. #11
    Forum Contributor
    Join Date
    02-06-2013
    Location
    Maryland
    MS-Off Ver
    Excel 2010
    Posts
    812

    Re: Lookup same value but return different results

    FlameRetired: Noted. Thanks.

+ 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 return multiple results
    By mente73 in forum Excel General
    Replies: 5
    Last Post: 12-04-2020, 01:50 PM
  2. Lookup and return multiple results
    By dw_22801 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-23-2019, 12:07 PM
  3. Lookup value from different lists and return different results
    By jackson_hollon in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 06-05-2017, 01:25 AM
  4. Lookup a Value and Return Multiple Results
    By tigeravatar in forum Tips and Tutorials
    Replies: 13
    Last Post: 04-08-2016, 04:44 PM
  5. Replies: 4
    Last Post: 05-19-2015, 08:42 PM
  6. Lookup a Value and Return Multiple Results
    By sgtkikass in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 05-06-2014, 10:06 PM
  7. Lookup & return Multiple Results
    By benishiryo in forum Excel Tips
    Replies: 2
    Last Post: 07-11-2012, 10:11 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