Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index lookup change data
I am trying to take this formula and move it from on column to another and
only change one value. When I go to edit the formula it removes the "{}" around the formula. The formula works in the original cells but If I add the "{}" it will not caluculate. {=IF(ISERROR(INDEX($T$1:$W$2500,SMALL(IF($T$1:$T$2 500=$A3,ROW($T$1:$T$2500)),ROW($8:$8)),4)),"",(IND EX($T$1:$W$2500,SMALL(IF($T$1:$T$2500=$A3,ROW($T$1 :$T$2500)),ROW($8:$8)),4)))} I just want to change row 8:8 to row 9:9 and so on. If I edit the original cell it changes the formula to remove the "{}" and the formula will no longer pull the data. Please help with how to correct this issue. Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index lookup change data
The squiggly brackets { } denote an array formula.
**Array formulas are entered differently than a regular formula. After you type in a regular formula you hit the ENTER key. With an array formula you *must* use a combination of keys. Those keys are the CTRL key, the SHIFT key and the ENTER key. That is, hold down both the CTRL key and the SHIFT key then hit the ENTER key. When done properly Excel will enclose the formula in squiggly brackets { }. You can't just type these brackets in, you *must* use the key combo to produce them. Also, anytime you edit an array formula it *must* be re-entered as an array using the key combo. As far as the formula itself, it can be made much more efficient. I'd use an intermediate cell to count how many records meet the criteria... Enter this formula in cell A1: =COUNTIF(T1:T2500,A3) Then, you refer to that cell and you can replace all this: {=IF(ISERROR(INDEX($T$1:$W$2500,SMALL(IF($T$1:$T$ 2500=$A3,ROW($T$1:$T$2500)),ROW($8:$8)),4)) With something like this: =IF(ROWS(A$1:A8)$A$1,"",........ Here's the entire array entered** formula: =IF(ROWS(A$1:A8)$A$1,"",INDEX($T:$T,SMALL(IF($T$1 :$T$2500=$A3,ROW(T$1:T$2500)),ROWS(A$1:A8)))) -- Biff Microsoft Excel MVP "John" wrote in message ... I am trying to take this formula and move it from on column to another and only change one value. When I go to edit the formula it removes the "{}" around the formula. The formula works in the original cells but If I add the "{}" it will not caluculate. {=IF(ISERROR(INDEX($T$1:$W$2500,SMALL(IF($T$1:$T$2 500=$A3,ROW($T$1:$T$2500)),ROW($8:$8)),4)),"",(IND EX($T$1:$W$2500,SMALL(IF($T$1:$T$2500=$A3,ROW($T$1 :$T$2500)),ROW($8:$8)),4)))} I just want to change row 8:8 to row 9:9 and so on. If I edit the original cell it changes the formula to remove the "{}" and the formula will no longer pull the data. Please help with how to correct this issue. Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index lookup change data
Ooops!
Correction. I indexed the wrong column. Should be (still array entered): =IF(ROWS(A$1:A8)$A$1,"",INDEX($W:$W,SMALL(IF($T$1 :$T$2500=$A3,ROW(T$1:T$2500)),ROWS(A$1:A8)))) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... The squiggly brackets { } denote an array formula. **Array formulas are entered differently than a regular formula. After you type in a regular formula you hit the ENTER key. With an array formula you *must* use a combination of keys. Those keys are the CTRL key, the SHIFT key and the ENTER key. That is, hold down both the CTRL key and the SHIFT key then hit the ENTER key. When done properly Excel will enclose the formula in squiggly brackets { }. You can't just type these brackets in, you *must* use the key combo to produce them. Also, anytime you edit an array formula it *must* be re-entered as an array using the key combo. As far as the formula itself, it can be made much more efficient. I'd use an intermediate cell to count how many records meet the criteria... Enter this formula in cell A1: =COUNTIF(T1:T2500,A3) Then, you refer to that cell and you can replace all this: {=IF(ISERROR(INDEX($T$1:$W$2500,SMALL(IF($T$1:$T $2500=$A3,ROW($T$1:$T$2500)),ROW($8:$8)),4)) With something like this: =IF(ROWS(A$1:A8)$A$1,"",........ Here's the entire array entered** formula: =IF(ROWS(A$1:A8)$A$1,"",INDEX($T:$T,SMALL(IF($T$1 :$T$2500=$A3,ROW(T$1:T$2500)),ROWS(A$1:A8)))) -- Biff Microsoft Excel MVP "John" wrote in message ... I am trying to take this formula and move it from on column to another and only change one value. When I go to edit the formula it removes the "{}" around the formula. The formula works in the original cells but If I add the "{}" it will not caluculate. {=IF(ISERROR(INDEX($T$1:$W$2500,SMALL(IF($T$1:$T$2 500=$A3,ROW($T$1:$T$2500)),ROW($8:$8)),4)),"",(IND EX($T$1:$W$2500,SMALL(IF($T$1:$T$2500=$A3,ROW($T$1 :$T$2500)),ROW($8:$8)),4)))} I just want to change row 8:8 to row 9:9 and so on. If I edit the original cell it changes the formula to remove the "{}" and the formula will no longer pull the data. Please help with how to correct this issue. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need help with INDEX lookup | Excel Worksheet Functions | |||
Lookup? Index? or something else? | Excel Discussion (Misc queries) | |||
How to stop index match returning data where lookup cell empty | Excel Worksheet Functions | |||
Either LOOKUP or INDEX Help | Excel Worksheet Functions | |||
Only text values matching using index/match lookup - data type pro | Excel Worksheet Functions |