Look up multiple instances of a value excluding duplicates I\'m using the array
ID: 3565038 • Letter: L
Question
Look up multiple instances of a value excluding duplicates
I'm using the array formula below to return the first 4 dates from Sheet 2 that correspond to the value in column J. The dates are listed in columns L - O. Column K counts the number of instances on Sheet 2 of each value in column J. This solution came from Biff's answer to a post on 6/26/2010 & works nicely.
I need to take this a step further & only show the first 4 unique dates. Basically, I need to adapt the array formula below to exclude duplicate dates. Any help will be greatly appreciated. I am happy to provide additional information as necessary. Please excuse any mistakes as this is my first time posting.
{=IF(COLUMNS($L2:L2)>$K2,"Noone",INDEX('Sheet 2'!$O:;$O,SMALL(IF('Sheeet 2'!$K$2:$K$15000=$J2,ROW('Sheet 2'!$K$2:$K$15000)),COLUMNS($L2:L2)))))}
I'm using the array formula below to return the first 4 dates from Sheet 2 that correspond to the value in column J. The dates are listed in columns L - O. Column K counts the number of instances on Sheet 2 of each value in column J. I need to take this a step further & only show the first 4 unique dates. Basically, I need to adapt the array formula below to exclude duplicate dates. Any help will be greatly appreciated. I am happy to provide additional information as necessary.Explanation / Answer
Hi,
I have solved a similar problem at this link - In cell L423, enter this array formula (Ctrl+Shift+Enter)
=IFERROR(IF(COLUMNS($L423:L423)>$K423,"None",INDEX('Sheet 2'!$O$2:$O$15000,SMALL(IF(FREQUENCY(IF('Sheet 2'!$K$2:$K$15000='Sheet 1'!$J423,MATCH('Sheet 2'!$O$2:$O$15000,'Sheet 2'!$O$2:$O$15000,0)),ROW('Sheet 2'!$O$2:$O$15000)-ROW('Sheet 2'!$O$1)),ROW('Sheet 2'!$O$2:$O$15000)-ROW('Sheet 2'!$O$1)),COLUMNS($L1:L1)))),"")
Hope this helpss.!!
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.