Look up multiple instances of a value excluding duplicates I\'m using the array

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,"None",INDEX('Sheet 2'!$O:$O,SMALL(IF('Sheet 2'!$K$2:$K$15000=$J2,ROW('Sheet 2'!$K$2:$K$15000)),COLUMNS($L2:L2))))}

Explanation / Answer

Hi,

I have solved a similar problem at this link - http://www.ashishmathur.com/dynamically-extract-unique-values-with-multiple-conditions/. 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 helps.

Related Questions

Navigate

• Browse (All)
• Browse L
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.