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.
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.
Last edited by excelnoob57; 05-05-2021 at 08:29 AM.
Please try,
Important this is an array: Please use Shift+Crtl+Enter.HTML Code:
Hope this helps
Last edited by D13L; 05-04-2021 at 05:35 AM. Reason: MISUNDERSTOOD REQUEST
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))),"")
Quang PT
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.
I have included the excel file because I seem to not get the desired result from the formulas above. Thanks everyone
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
Last edited by JohnTopley; 05-04-2021 at 03:36 PM.
excelnoob57: See solution in column M2:M6. If this is the solution you are looking for, mark thread as solved.
@ 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
Similarly in cell E4:
Sorts and returns duplicated ages directly rather than row numbers.
Formula:Please Login or Register to view this content.
Last edited by FlameRetired; 05-04-2021 at 11:42 PM.
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)),"")
Pl note
Array formula should be confirmed with Ctrl+Shift+Enter keys together.
If answere is satisfactory press * to add reputation.
FlameRetired: Noted. Thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks