| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
copying a formula down wards
=INDEX(Proj_code,MATCH(AQ$6,F$7:F$198,0))
the above formula works in that it shows the first match. As there could be mulitpule records what do I need to do to so when I copy it down it will bring the next match or 0 if no further matches in the table it is searching? Many thanks UKMAN1 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
copying a formula down wards
Hi,
Try this ARRAY formula. ARRAY enter it and it will return the first match, drag down for the second etc. It will return an error if there isn't a second match so you could wrap the whole thing =isserror(formula etc =OFFSET(IF(ROWS(B$7:B7)<=COUNTIF(Proj_code,$E$1),I NDEX($F$7:$F$198,SMALL(IF(Proj_code=$AQ$6,ROW(Proj _code)-ROW($E$1)+1),ROWS(B$7:B7))),""),-6,0) This is an array formula which must be entered by pressing CTRL+Shift+Enter and not just Enter. If you do it correctly then Excel will put curly brackets around the formula {}. You can't type these yourself. If you edit the formula you must enter it again with CTRL+Shift+Enter. -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "UKMAN" wrote: =INDEX(Proj_code,MATCH(AQ$6,F$7:F$198,0)) the above formula works in that it shows the first match. As there could be mulitpule records what do I need to do to so when I copy it down it will bring the next match or 0 if no further matches in the table it is searching? Many thanks UKMAN1 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
copying a formula down wards
Select the range of cells in which you want the results, say L18:L25.
Assuming you have numeric values in D4:D11 and the corresponding values to return in E4:E11, enter the following array formula and press CTRL SHIFT ENTER. You must use CTRL SHIFT ENTER to enter the formula into an array of cells, rather than a single cell. =IF(ROW()-ROW(L$18)<COUNTIF(D4:D11,"b"),LARGE(IF(D4:D11="b", E4:E11,0),ROW(INDIRECT("1:"&COUNTIF(D4:D11,"b")))) ,0) Change the reference to L$18 to the first cell in the results range that contains the formula. Change the "b" to the value you want to look up in D4:D11. This is an array formula, so you MUST press CTRL SHIFT ENTER rather than just ENTER when you first enter the formula and whenever you edit it later. If you do this correctly, Excel will display the formula in the formula bar enclosed in curly braces { }. You don't type in the braces; Excel puts them there automatically. The formula will not work correctly if you do not enter it with CTRL SHIFT ENTER. See www.cpearson.com/Excel/ArrayFormulas.aspx for much more information about array formulas. Cordially, Chip Pearson Microsoft Most Valuable Professional, Excel, 1998 - 2010 Pearson Software Consulting, LLC www.cpearson.com On Thu, 15 Apr 2010 09:08:01 -0700, UKMAN wrote: =INDEX(Proj_code,MATCH(AQ$6,F$7:F$198,0)) the above formula works in that it shows the first match. As there could be mulitpule records what do I need to do to so when I copy it down it will bring the next match or 0 if no further matches in the table it is searching? Many thanks UKMAN1 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
copying a formula down wards
Chip, thanks for the reply but :(
below is the formula and my changes but it says too many arquements?? excel hights the first ",0". For clarity in cell ref in the hope I have done the correct changes: AN$9 is the first line/cell for the report results f7:f198 is the range of value in AQ6 (both text)is to match b7:b198 is where the value (i.e. PC01) to be return to an9 IF(ROW()-ROW(AN$9)<COUNTIF(F$7:F$198,$AQ$6),LARGE(IF(F$7:F$ 198,$AQ$6,B$7:B$198,0),ROW(INDIRECT("1:"&COUNTIF(F $7:F$198,$AQ$6)))),0) I do thank you for your help as I am trying to understand the nore indepth formulas UKMAN1 "Chip Pearson" wrote: Select the range of cells in which you want the results, say L18:L25. Assuming you have numeric values in D4:D11 and the corresponding values to return in E4:E11, enter the following array formula and press CTRL SHIFT ENTER. You must use CTRL SHIFT ENTER to enter the formula into an array of cells, rather than a single cell. =IF(ROW()-ROW(L$18)<COUNTIF(D4:D11,"b"),LARGE(IF(D4:D11="b", E4:E11,0),ROW(INDIRECT("1:"&COUNTIF(D4:D11,"b")))) ,0) Change the reference to L$18 to the first cell in the results range that contains the formula. Change the "b" to the value you want to look up in D4:D11. This is an array formula, so you MUST press CTRL SHIFT ENTER rather than just ENTER when you first enter the formula and whenever you edit it later. If you do this correctly, Excel will display the formula in the formula bar enclosed in curly braces { }. You don't type in the braces; Excel puts them there automatically. The formula will not work correctly if you do not enter it with CTRL SHIFT ENTER. See www.cpearson.com/Excel/ArrayFormulas.aspx for much more information about array formulas. Cordially, Chip Pearson Microsoft Most Valuable Professional, Excel, 1998 - 2010 Pearson Software Consulting, LLC www.cpearson.com On Thu, 15 Apr 2010 09:08:01 -0700, UKMAN wrote: =INDEX(Proj_code,MATCH(AQ$6,F$7:F$198,0)) the above formula works in that it shows the first match. As there could be mulitpule records what do I need to do to so when I copy it down it will bring the next match or 0 if no further matches in the table it is searching? Many thanks UKMAN1 . |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
copying a formula down wards
Try this...
Enter this formula in A1. This will return the count of records that meet the criteria. =COUNTIF(F$7:F$198,AQ$6) Enter this array formula** in B1 and copy down until you get 0s.. This will extract the records that meet the criteria. =IF(ROWS(B$1:B1)A$1,0,INDEX(Proj_code,SMALL(IF(F$ 7:F$198=AQ$6,ROW(Proj_code)),ROWS(B$1:B1))-MIN(ROW(Proj_code))+1)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "UKMAN" wrote in message ... =INDEX(Proj_code,MATCH(AQ$6,F$7:F$198,0)) the above formula works in that it shows the first match. As there could be mulitpule records what do I need to do to so when I copy it down it will bring the next match or 0 if no further matches in the table it is searching? Many thanks UKMAN1 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
copying a formula down wards
Too many arguments is referring to this part of your formula:
IF(F$7:F$198, $AQ$6, B$7:B$198, 0) because an IF function only requires 3 arguments. I suspect you meant: IF(F$7:F$198=$AQ$6,B$7:B$198,0) "UKMAN" wrote in message ... Chip, thanks for the reply but :( below is the formula and my changes but it says too many arquements?? excel hights the first ",0". For clarity in cell ref in the hope I have done the correct changes: AN$9 is the first line/cell for the report results f7:f198 is the range of value in AQ6 (both text)is to match b7:b198 is where the value (i.e. PC01) to be return to an9 IF(ROW()-ROW(AN$9)<COUNTIF(F$7:F$198,$AQ$6),LARGE(IF(F$7:F$ 198,$AQ$6,B$7:B$198,0),ROW(INDIRECT("1:"&COUNTIF(F $7:F$198,$AQ$6)))),0) I do thank you for your help as I am trying to understand the nore indepth formulas UKMAN1 "Chip Pearson" wrote: Select the range of cells in which you want the results, say L18:L25. Assuming you have numeric values in D4:D11 and the corresponding values to return in E4:E11, enter the following array formula and press CTRL SHIFT ENTER. You must use CTRL SHIFT ENTER to enter the formula into an array of cells, rather than a single cell. =IF(ROW()-ROW(L$18)<COUNTIF(D4:D11,"b"),LARGE(IF(D4:D11="b", E4:E11,0),ROW(INDIRECT("1:"&COUNTIF(D4:D11,"b")))) ,0) Change the reference to L$18 to the first cell in the results range that contains the formula. Change the "b" to the value you want to look up in D4:D11. This is an array formula, so you MUST press CTRL SHIFT ENTER rather than just ENTER when you first enter the formula and whenever you edit it later. If you do this correctly, Excel will display the formula in the formula bar enclosed in curly braces { }. You don't type in the braces; Excel puts them there automatically. The formula will not work correctly if you do not enter it with CTRL SHIFT ENTER. See www.cpearson.com/Excel/ArrayFormulas.aspx for much more information about array formulas. Cordially, Chip Pearson Microsoft Most Valuable Professional, Excel, 1998 - 2010 Pearson Software Consulting, LLC www.cpearson.com On Thu, 15 Apr 2010 09:08:01 -0700, UKMAN wrote: =INDEX(Proj_code,MATCH(AQ$6,F$7:F$198,0)) the above formula works in that it shows the first match. As there could be mulitpule records what do I need to do to so when I copy it down it will bring the next match or 0 if no further matches in the table it is searching? Many thanks UKMAN1 . |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
copying a formula down wards
Mr T,
many thanks and it worked perfectly. To all others many thanks as well for your help. Regards UKMAN1 "T. Valko" wrote: Try this... Enter this formula in A1. This will return the count of records that meet the criteria. =COUNTIF(F$7:F$198,AQ$6) Enter this array formula** in B1 and copy down until you get 0s.. This will extract the records that meet the criteria. =IF(ROWS(B$1:B1)A$1,0,INDEX(Proj_code,SMALL(IF(F$ 7:F$198=AQ$6,ROW(Proj_code)),ROWS(B$1:B1))-MIN(ROW(Proj_code))+1)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "UKMAN" wrote in message ... =INDEX(Proj_code,MATCH(AQ$6,F$7:F$198,0)) the above formula works in that it shows the first match. As there could be mulitpule records what do I need to do to so when I copy it down it will bring the next match or 0 if no further matches in the table it is searching? Many thanks UKMAN1 . |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
copying a formula down wards
Steve,
many thanks for your help. UKMAN1 "Steve Dunn" wrote: Too many arguments is referring to this part of your formula: IF(F$7:F$198, $AQ$6, B$7:B$198, 0) because an IF function only requires 3 arguments. I suspect you meant: IF(F$7:F$198=$AQ$6,B$7:B$198,0) "UKMAN" wrote in message ... Chip, thanks for the reply but :( below is the formula and my changes but it says too many arquements?? excel hights the first ",0". For clarity in cell ref in the hope I have done the correct changes: AN$9 is the first line/cell for the report results f7:f198 is the range of value in AQ6 (both text)is to match b7:b198 is where the value (i.e. PC01) to be return to an9 IF(ROW()-ROW(AN$9)<COUNTIF(F$7:F$198,$AQ$6),LARGE(IF(F$7:F$ 198,$AQ$6,B$7:B$198,0),ROW(INDIRECT("1:"&COUNTIF(F $7:F$198,$AQ$6)))),0) I do thank you for your help as I am trying to understand the nore indepth formulas UKMAN1 "Chip Pearson" wrote: Select the range of cells in which you want the results, say L18:L25. Assuming you have numeric values in D4:D11 and the corresponding values to return in E4:E11, enter the following array formula and press CTRL SHIFT ENTER. You must use CTRL SHIFT ENTER to enter the formula into an array of cells, rather than a single cell. =IF(ROW()-ROW(L$18)<COUNTIF(D4:D11,"b"),LARGE(IF(D4:D11="b", E4:E11,0),ROW(INDIRECT("1:"&COUNTIF(D4:D11,"b")))) ,0) Change the reference to L$18 to the first cell in the results range that contains the formula. Change the "b" to the value you want to look up in D4:D11. This is an array formula, so you MUST press CTRL SHIFT ENTER rather than just ENTER when you first enter the formula and whenever you edit it later. If you do this correctly, Excel will display the formula in the formula bar enclosed in curly braces { }. You don't type in the braces; Excel puts them there automatically. The formula will not work correctly if you do not enter it with CTRL SHIFT ENTER. See www.cpearson.com/Excel/ArrayFormulas.aspx for much more information about array formulas. Cordially, Chip Pearson Microsoft Most Valuable Professional, Excel, 1998 - 2010 Pearson Software Consulting, LLC www.cpearson.com On Thu, 15 Apr 2010 09:08:01 -0700, UKMAN wrote: =INDEX(Proj_code,MATCH(AQ$6,F$7:F$198,0)) the above formula works in that it shows the first match. As there could be mulitpule records what do I need to do to so when I copy it down it will bring the next match or 0 if no further matches in the table it is searching? Many thanks UKMAN1 . |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
copying a formula down wards
Mike,
many thanks for your help. UKMAN1 "Mike H" wrote: Hi, Try this ARRAY formula. ARRAY enter it and it will return the first match, drag down for the second etc. It will return an error if there isn't a second match so you could wrap the whole thing =isserror(formula etc =OFFSET(IF(ROWS(B$7:B7)<=COUNTIF(Proj_code,$E$1),I NDEX($F$7:$F$198,SMALL(IF(Proj_code=$AQ$6,ROW(Proj _code)-ROW($E$1)+1),ROWS(B$7:B7))),""),-6,0) This is an array formula which must be entered by pressing CTRL+Shift+Enter and not just Enter. If you do it correctly then Excel will put curly brackets around the formula {}. You can't type these yourself. If you edit the formula you must enter it again with CTRL+Shift+Enter. -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "UKMAN" wrote: =INDEX(Proj_code,MATCH(AQ$6,F$7:F$198,0)) the above formula works in that it shows the first match. As there could be mulitpule records what do I need to do to so when I copy it down it will bring the next match or 0 if no further matches in the table it is searching? Many thanks UKMAN1 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
copying a formula down wards
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "UKMAN" wrote in message ... Mr T, many thanks and it worked perfectly. To all others many thanks as well for your help. Regards UKMAN1 "T. Valko" wrote: Try this... Enter this formula in A1. This will return the count of records that meet the criteria. =COUNTIF(F$7:F$198,AQ$6) Enter this array formula** in B1 and copy down until you get 0s.. This will extract the records that meet the criteria. =IF(ROWS(B$1:B1)A$1,0,INDEX(Proj_code,SMALL(IF(F$ 7:F$198=AQ$6,ROW(Proj_code)),ROWS(B$1:B1))-MIN(ROW(Proj_code))+1)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "UKMAN" wrote in message ... =INDEX(Proj_code,MATCH(AQ$6,F$7:F$198,0)) the above formula works in that it shows the first match. As there could be mulitpule records what do I need to do to so when I copy it down it will bring the next match or 0 if no further matches in the table it is searching? Many thanks UKMAN1 . |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
copying a formula down wards
Hi,
You may refer to my article here - http://office.microsoft.com/en-gb/ex...260381033.aspx -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "UKMAN" wrote in message ... =INDEX(Proj_code,MATCH(AQ$6,F$7:F$198,0)) the above formula works in that it shows the first match. As there could be mulitpule records what do I need to do to so when I copy it down it will bring the next match or 0 if no further matches in the table it is searching? Many thanks UKMAN1 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| WHEN ENTER NUMBER , DISPLAY IN WARDS ? | Excel Worksheet Functions | |||
| HOW WE CAN FIT THE TEXT MESSAGES IN SINGLE COLUNM UP/DOWN WARDS | New Users to Excel | |||
| copy formula result (text) only - without copying formula | Excel Discussion (Misc queries) | |||
| copying the Hyperlink function result without copying the actual formula | Excel Worksheet Functions | |||
| Formula not copying down | Excel Worksheet Functions | |||
Linear Mode
