| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find and return multiple values
Hi,
I've an urgent question here in regards to return multiple values and hope you can help. Sheet1 A B C D E ... 1 1001 2 1002 3 1003 4 1004 Sheet2 A B 1 1002 Y01 2 1001 B01 3 1004 Z08 4 1001 D04 5 1001 J05 6 1004 H22 Let's say in Sheet1 cell B1, I type in =vlookup(A1, Sheet2!A:B,2,false), it returns the value "B01". Is it possible to type in formula in cell C1, D1 to return the remaining match, which are "D04" and "J05"? Your reply is much appreciated, BubbleGum |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Find and return multiple values
Try in B1:
=INDEX(Sheet2!$B$1:$B$6,SMALL(IF(Sheet2!$A$1:$A$6= $A1,ROW(Sheet2!$B$1:$B$6)-ROW(Sheet2!$B$1)+1),COLUMNS($B1:B1))) array entered w/ Cntrl+Shift+Enter. Copy across until you get #NUM If you prefer blank instead of #NUM then try =IF(SUM(--(Sheet2!$A$1:$A$6=$A1))<COLUMNS($B1:B1),"",INDEX(S heet2!$B$1:$B$6,SMALL(IF(Sheet2!$A$1:$A$6=$A1,ROW( Sheet2!$B$1:$B$6)-ROW(Sheet2!$B$1)+1),COLUMNS($B1:B1)))) also array entered. "BubbleGum" wrote: Hi, I've an urgent question here in regards to return multiple values and hope you can help. Sheet1 A B C D E ... 1 1001 2 1002 3 1003 4 1004 Sheet2 A B 1 1002 Y01 2 1001 B01 3 1004 Z08 4 1001 D04 5 1001 J05 6 1004 H22 Let's say in Sheet1 cell B1, I type in =vlookup(A1, Sheet2!A:B,2,false), it returns the value "B01". Is it possible to type in formula in cell C1, D1 to return the remaining match, which are "D04" and "J05"? Your reply is much appreciated, BubbleGum |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Find and return multiple values
Enter this array formula in B1
=IF( COLUMNS( $B1:B1 ) COUNTIF( Sheet2!$A$1:$A$6, $A1 ), "-", INDEX( Sheet2!$B:$B, SMALL( IF( $A1 = Sheet2!$A$1:$A$6, ROW( Sheet2!$A$1:$A$6 ) ), COLUMNS( $B1:B1 ) ) ) ) Drag/Fill as needed... -- Regards, Luc. "Festina Lente" "BubbleGum" wrote: Hi, I've an urgent question here in regards to return multiple values and hope you can help. Sheet1 A B C D E ... 1 1001 2 1002 3 1003 4 1004 Sheet2 A B 1 1002 Y01 2 1001 B01 3 1004 Z08 4 1001 D04 5 1001 J05 6 1004 H22 Let's say in Sheet1 cell B1, I type in =vlookup(A1, Sheet2!A:B,2,false), it returns the value "B01". Is it possible to type in formula in cell C1, D1 to return the remaining match, which are "D04" and "J05"? Your reply is much appreciated, BubbleGum |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Lookup in Multiple Columns, Return Multiple Values | Excel Worksheet Functions | |||
| find largest values, then return corresponding row values. | Excel Discussion (Misc queries) | |||
| Find Multiple instances of Single Criterion in Row & Return To a Single Col | Excel Worksheet Functions | |||
| Find two values in worksheet to return one value | Excel Worksheet Functions | |||
| Look up one value and return multiple corresponding values in exce | Excel Discussion (Misc queries) | |||
Hybrid Mode
