LOOKUP multiple results but ignore duplicates.
 

Hi everyone!

I have this formula below it will return multiple results is it
possilbe to modified it that it will return multiple results
_but_ignore_duplicates._ I would like it to be a stand alone formula no
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)))


--
vane0326
------------------------------------------------------------------------
vane0326's Profile: http://www.excelforum.com/member.php...o&userid=14731
View this thread: http://www.excelforum.com/showthread...hreadid=546922

LOOKUP multiple results but ignore duplicates.
 

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

"vane0326" wrote in
message ...

Hi everyone!

I have this formula below it will return multiple results is it
possilbe to modified it that it will return multiple results
_but_ignore_duplicates._ I would like it to be a stand alone formula no
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)))


--
vane0326
------------------------------------------------------------------------
vane0326's Profile:
http://www.excelforum.com/member.php...o&userid=14731
View this thread: http://www.excelforum.com/showthread...hreadid=546922

LOOKUP multiple results but ignore duplicates.
 

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

Biff

"Biff" wrote in message
...
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

"vane0326" wrote
in message ...

Hi everyone!

I have this formula below it will return multiple results is it
possilbe to modified it that it will return multiple results
_but_ignore_duplicates._ I would like it to be a stand alone formula no
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)))


--
vane0326
------------------------------------------------------------------------
vane0326's Profile:
http://www.excelforum.com/member.php...o&userid=14731
View this thread:
http://www.excelforum.com/showthread...hreadid=546922

LOOKUP multiple results but ignore duplicates.
 

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*?*


--
vane0326
------------------------------------------------------------------------
vane0326's Profile: http://www.excelforum.com/member.php...o&userid=14731
View this thread: http://www.excelforum.com/showthread...hreadid=546922

LOOKUP multiple results but ignore duplicates.
 

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" wrote in
message ...

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*?*


--
vane0326
------------------------------------------------------------------------
vane0326's Profile:
http://www.excelforum.com/member.php...o&userid=14731
View this thread: http://www.excelforum.com/showthread...hreadid=546922

LOOKUP multiple results but ignore duplicates.
 

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 |
+-------------------------------------------------------------------+

--
vane0326
------------------------------------------------------------------------
vane0326's Profile: http://www.excelforum.com/member.php...o&userid=14731
View this thread: http://www.excelforum.com/showthread...hreadid=546922

LOOKUP multiple results but ignore duplicates.
 

Try...

F2, copied down:

=INDEX(B$2:B$100,SMALL(IF(A$2:A$100=E$2,IF(B$2:B$1 00<"",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!

In article ,
vane0326
wrote:

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 |
+-------------------------------------------------------------------+

LOOKUP multiple results but ignore duplicates.
 

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 |
+-------------------------------------------------------------------+

--
vane0326
------------------------------------------------------------------------
vane0326's Profile: http://www.excelforum.com/member.php...o&userid=14731
View this thread: http://www.excelforum.com/showthread...hreadid=546922

LOOKUP multiple results but ignore duplicates.
 

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$1 00<"",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!

In article ,
vane0326 wrote:

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 |
+-------------------------------------------------------------------+

LOOKUP multiple results but ignore duplicates.
 

Thanks Domenic it works great.


--
vane0326
------------------------------------------------------------------------
vane0326's Profile: http://www.excelforum.com/member.php...o&userid=14731
View this thread: http://www.excelforum.com/showthread...hreadid=546922

LOOKUP multiple results but ignore duplicates.
 

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

Biff

"Domenic" wrote in message
...
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$1 00<"",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!

In article ,
vane0326 wrote:

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 |
+-------------------------------------------------------------------+