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

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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

Biff

"Biff" <[email protected]> wrote in message
news:%23Y%[email protected]...
> 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" <[email protected]> wrote
> in message news:[email protected]...
>>
>> 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
>>
>
>

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?

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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?

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!

In article <[email protected]>,
vane0326 <[email protected]>
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 |
> +-------------------------------------------------------------------+

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!

In article <[email protected]>,
vane0326 <[email protected]> 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 |
> +-------------------------------------------------------------------+

Thanks Domenic it works great.

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

Biff

"Domenic" <[email protected]> wrote in message
news:[email protected]...
> 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!
>
> In article <[email protected]>,
> vane0326 <[email protected]> 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 |
>> +-------------------------------------------------------------------+

Thanks for attaching the excel file...it helps to understand the formula and use it.
Good work all.

I need formula to allocate times for siblings in each group. there are 55 groups, with 22-25 children in each, some of them have 2,3,or 4 siblings.
Appointment last 10 mins, then if siblings: I added 10 minutes time to get to another room for another app, if no siblin: only 5 min between app is needed.

groups look like:
child A Group A SIBLING GROUP C SIBLING A GROUP E
child B Group A
child C Group A
child D Group A
child E Group B
child F Group B
SIBLING GROUP C
SIBLING A GROUP E

siblings are in different groups, appointments for siblings look like that:
(first sibling app is 8:40, second at 9:00 third at 9:20, then if 1st sibling app is at 8:55 next app is 9:15 then break and app resume at 10:15......
so app for 4 siblings must start when 4 times are available so parents dont have to wait during staff break or long lunch.
I've got app times in a table:
08:40 09:00 09:20
08:55 09:15
break
10:15 10:30
10:30 10:45 11:00 11:15 11:30
10:45 11:05 11:25 11:45
11:00 11:20 11:40
lunch
13:15 13:35 13:55 14:15 14:35
13:30 13:50 14:10 14:30

Siblings are in different groups and have appointments within 20 minutes.
Then I've got all NON SIBLINGS: I can not use the app time allocated to any sibling who's in the same group as no sibling.

I'm trying to use index/match and small bur getting times repeated in the same group and i can not have 2 parents turning up at the same time!
Could you help please? pretty urgent
Thank you

Hello melvil007, and welcome to the forum.

Unfortunately you have inadvertently broken one of the forum rules. Please read the following and make the necessary change. Thanks.

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.

Im having a similar problem. I have one Sheet "Dashboard" and the sheet im using the formula on "Test".

Here's a sample of the formula im currently using: (on "Test" sheet cells B24:B26)

=INDEX(DASHBOARD!B53:B61,MATCH(O24,DASHBOARD!W53:W61,0))

-Dashboard B53:B61 contains the company names (to be listed in Cells B24:B26)
-024 contains the percentage in which to get the matching company name (listed in Cells O24:O26)
-DASHBOARD!W53:W61 contains the percentages of all the companies

If I get two companies with the same percentages I want to have them both listed, not the same one twice.

My backup plan was to created a macro that would change the percentages by .002% but I'd rather solve it with a formula if possible.

Thanks so much!