Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
TO COPY MORE THAN INE RESULT FROM OTHER SHEET USING LOOKUP
Sirs
I want formula for one criteria & with more than one result. Please refer example Sheet-1 Sheet-2 Col-a Col-a col-b aa-01 xy-001 aa-02 aa-02 xs-001 cc-01 cc-01 xx-002 aa-02 cc-02 yz-001 cc-02 I want result like in Sheet-1 at Col-b & C Sheet-1 Col-a col-b col-c aa-01 aa-02 xy-001 xx-002 cc-01 xs-001 cc-02 yz-001 Datas will be more than 10000 entries. Please help on this Regards Peranish |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
TO COPY MORE THAN INE RESULT FROM OTHER SHEET USING LOOKUP
XL-2007:
=IFERROR(INDEX(rngA,SMALL(IF(rngB=$A2,ROW(INDIRECT ("1:"&ROWS(rngA)))),COLUMNS($B:B))),"") ctrl+shift+enter, not just enter copy across and down as far as needed Prior to XL-2007: =IF(ISERR(SMALL(IF(rngB=$A2,ROW(INDIRECT("1:"&ROWS (rngA)))),COLUMNS($B:B))),"",INDEX(rngA,SMALL(IF(r ngB=$A2,ROW(INDIRECT("1:"&ROWS(rngA)))),COLUMNS($B :B)))) ctrl+shift+enter, not just enter copy across and down as far as needed "PERANISH" wrote: Sirs I want formula for one criteria & with more than one result. Please refer example Sheet-1 Sheet-2 Col-a Col-a col-b aa-01 xy-001 aa-02 aa-02 xs-001 cc-01 cc-01 xx-002 aa-02 cc-02 yz-001 cc-02 I want result like in Sheet-1 at Col-b & C Sheet-1 Col-a col-b col-c aa-01 aa-02 xy-001 xx-002 cc-01 xs-001 cc-02 yz-001 Datas will be more than 10000 entries. Please help on this Regards Peranish |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
TO COPY MORE THAN INE RESULT FROM OTHER SHEET USING LOOKUP
One play which produces the desired transformation
Illustrated in this sample: http://freefilehosting.net/download/41mae Extract multiple results horizontal.xls Source data assumed in Sheet2, in A2:B2 down In Sheet1, List the unique Sheet2's col B source items in A1:D1 In A2: =IF(Sheet2!$B2=A$1,ROW(),"") Copy A2 across to D2, fill down to cover the max expected extent of source data in Sheet1 In F2, copied down to F5: =INDEX($A$1:$D$1,ROWS($1:1)) In G2: =IF(COLUMNS($A:A)COUNT(OFFSET($A:$A,,ROWS($1:1)-1)),"", INDEX(Sheet2!$A:$A,SMALL(OFFSET($A:$A,,ROWS($1:1)-1),COLUMNS($A:A)))) Copy G2 across as far as required, fill down to return the desired results. -- Max Singapore http://savefile.com/projects/236895 Downloads:20,500 Files:362 Subscribers:64 xdemechanik --- Sheet-1 Sheet-2 Col-a Col-a col-b aa-01 xy-001 aa-02 aa-02 xs-001 cc-01 cc-01 xx-002 aa-02 cc-02 yz-001 cc-02 I want result like in Sheet-1 at Col-b & C Sheet-1 Col-a col-b col-c aa-01 aa-02 xy-001 xx-002 cc-01 xs-001 cc-02 yz-001 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
TO COPY MORE THAN INE RESULT FROM OTHER SHEET USING LOOKUP
Hi,
You did not say how many column to the right that you might have, so here is a formula (this formula is array entered - Press Shift+Ctrl+Enter to enter it): =INDEX(Sheet2!$A$2:$A$5,LARGE(($A1=Sheet2!$B$2:$B$ 5)*(ROW(Sheet2!$B$2:$B$5))-1,COLUMN(A1))) Assume the data on sheet1 starts in A1 and the data on sheet2 starts on A2. Then enter the above formula in cell B1 of sheet1 and copy it down and to the right as far as necessary. It will return errors for all items not found but you can either format so that they don't display or you can delete them all with one command F5, Special, Formulas, Errors, then Del. Or you can use this longer version: =IF(ISERR(INDEX(Sheet2!$A$2:$A$5,LARGE(($A1=Sheet2 !$B$2:$B$5)*(ROW(Sheet2!$B$2:$B$5))-1,COLUMN(A1)))),"",INDEX(Sheet2!$A$2:$A$5,LARGE(($ A1=Sheet2!$B$2:$B$5)*(ROW(Sheet2!$B$2:$B$5))-1,COLUMN(A1)))) -- Thanks, Shane Devenshire "PERANISH" wrote: Sirs I want formula for one criteria & with more than one result. Please refer example Sheet-1 Sheet-2 Col-a Col-a col-b aa-01 xy-001 aa-02 aa-02 xs-001 cc-01 cc-01 xx-002 aa-02 cc-02 yz-001 cc-02 I want result like in Sheet-1 at Col-b & C Sheet-1 Col-a col-b col-c aa-01 aa-02 xy-001 xx-002 cc-01 xs-001 cc-02 yz-001 Datas will be more than 10000 entries. Please help on this Regards Peranish |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I annotate a formula result in Sheet 1 in Sheet 2 by Cell Addr | Excel Worksheet Functions | |||
Lookup for more than one result | Excel Discussion (Misc queries) | |||
Join 2 Lists - Lookup value in 1 list & use result in 2nd lookup | Excel Worksheet Functions | |||
I need a Lookup to return more than 1 result | Excel Worksheet Functions | |||
Unwanted LOOKUP() Result | Excel Discussion (Misc queries) |