| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 | +-------------------------------------------------------------------+ |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 | +-------------------------------------------------------------------+ |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 | +-------------------------------------------------------------------+ |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| find multiple results | Excel Discussion (Misc queries) | |||
| Multiple criteria LOOKUP | Excel Worksheet Functions | |||
| abdualmohsn | Excel Discussion (Misc queries) | |||
| lookup multiple occurrences of a value excel | Excel Worksheet Functions | |||
| lookup help with multiple hits | Excel Worksheet Functions | |||
Linear Mode
