LOOKUP multiple results but ignore duplicates.

[vane0326]

Hi everyone!

I have this formula below it will return multiple results is i
possilbe to modified it that it will return multiple result
_but_ignore_duplicates._ I would like it to be a stand alone formula n
helper cells or helper columns.


=INDEX(B$2:B$15,SMALL(IF(A$2:A$15=E$2,ROW(A$2:A$15)-ROW($A$2)+1),ROW(A1))

 

[Biff]

Hi!

Try this:

=INDEX(B$2:B$15,SMALL(IF(N(FREQUENCY(IF(A$2:A$15=E$2,MATCH(B$2:B$15,B$2:B$15,0)),MATCH(B$2:B$15,B$2:B$15,0))>0)>0,ROW(B$2:B$15)-ROW(B$2)+1),ROWS($1:1)))

Biff

 

[Biff]

That's an array formula. Ir needs to be entered with the key combination of
CTRL,SHIFT,ENTER.

Biff

 

[vane0326]

Thank You so much Biff the formula you provided works but when I try to
expand the range I get a #N/A! error.

=INDEX(B$2:B$100,SMALL(IF(N(FREQUENCY(IF(A$2:A$100=E$2,MATCH(B$2:B$100,B$2:B$100,0)),MATCH(B$2:B$100,B$2:B$100,0))>0)>0,ROW(B$2:B$100)-ROW(B$2)+1),ROWS($1:1)))


Is it because there are some blanks in the column B*?*

 

[Biff]

Hi!

Is it because there are some blanks in the column B*?*

No. If a cell in column A equals E2 and the corresponding cell in column B
is EMPTY and is the first instance of EMPTY then the formula will just
return 0 for that instance. The same thing will happen if a cell in column A
equals E2 and the corresponding cell in column B is a formula blank "". The
formula will return the formula blank for that instance.

Do you have instances where column A will equal E2 and the corresponding
cell in B2 will be either empty or a formula blank?

Do you have formulas in coulmn B that are returning an #N/A error?

Biff

 

[vane0326]

Hi Biff maybe I'm looking over something so I attach a sample file
below. Please let me know what I'm doing wrong. What do you think*?*


+-------------------------------------------------------------------+
|Filename: Lookup Ignore Duplicates.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4825 |
+-------------------------------------------------------------------+

 

[Domenic]

Try...

F2, copied down:

=INDEX(B$2:B$100,SMALL(IF(A$2:A$100=E$2,IF(B$2:B$100<>"",IF(MATCH(B$2:B$1
00,B$2:B$100,0)=ROW(B$2:B$100)-ROW(B$2)+1,ROW(B$2:B$15)-ROW(B$2)+1))),ROW
S(F$2:F2)))

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[vane0326]

Hi Domenic nice to see you. I tested your formula and its not picking up
all the results. I attach the file below. Look at the texts that are
red. The formula is not picking up those results.


+-------------------------------------------------------------------+
|Filename: Lookup Ignore Duplicates 1.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4827 |
+-------------------------------------------------------------------+

 

[Domenic]

Hi Vane!

Sorry, my mistake! Try the following instead...

=INDEX(B$2:B$100,SMALL(IF(A$2:A$100=E$2,IF(MATCH(A$2:A$100&B$2:B$100,A$2:
A$100&B$2:B$100,0)=ROW(B$2:B$100)-ROW(B$2)+1,ROW(B$2:B$100)-ROW(B$2)+1)),
ROWS(F$2:F2)))

If the corresponding value in Column B can contain an empty cell, and
you don't want a zero returned, try...

=INDEX(B$2:B$100,SMALL(IF(A$2:A$100=E$2,IF(B$2:B$100<>"",IF(MATCH(A$2:A$1
00&B$2:B$100,A$2:A$100&B$2:B$100,0)=ROW(B$2:B$100)-ROW(B$2)+1,ROW(B$2:B$1
00)-ROW(B$2)+1))),ROWS(F$2:F2)))

Hope this helps!

 

[vane0326]

Thanks Domenic it works great

 

[Biff]

I like your formula over mine. I figured this was easier than I made it out
to be!

Biff