Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array Formula
=INDEX(I16:I21,SMALL(IF(ISERROR(J16:J21),ROW
(J16:J21)),1),1) Why will this formula not work when I change the cells to the ones above in this formula? Its like the formula will not work unless the data starts on row 1. -----Original Message----- =INDEX(A1:A200,SMALL(IF(ISERROR(B1:B200),ROW (B1:B200)),1),1) Entered with Ctrl+Shift+Enter Rather than Just Enter since it is an array formula. -- Regards, Tom Ogilvy Todd Huttenstine wrote in message ... ColumnA ColumnB ColumnC C 1 *formula? D 2 B 3 E 4 F 5 A #N/A Above is the situation. I have formulas in column A and in Column B. The formulas in Column A will produce a letter. The formulas in Column B will produce a number. An #N/A error will be in only one of the cells in Column B in the specified range. I need a formula in cell C1 that will look in Columns A and B and when it sees the #N/A error in the cell in Column B, it will return the corresponding letter in Column A. So in this example, the formula in cell C1 will produce the result A, because the #N/A error is in cell B6. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array Formula
Answered you original posting of this question. Please stay in the thread.
-- Regards, Tom Ogilvy Todd Huttenstine wrote in message ... =INDEX(I16:I21,SMALL(IF(ISERROR(J16:J21),ROW (J16:J21)),1),1) Why will this formula not work when I change the cells to the ones above in this formula? Its like the formula will not work unless the data starts on row 1. -----Original Message----- =INDEX(A1:A200,SMALL(IF(ISERROR(B1:B200),ROW (B1:B200)),1),1) Entered with Ctrl+Shift+Enter Rather than Just Enter since it is an array formula. -- Regards, Tom Ogilvy Todd Huttenstine wrote in message ... ColumnA ColumnB ColumnC C 1 *formula? D 2 B 3 E 4 F 5 A #N/A Above is the situation. I have formulas in column A and in Column B. The formulas in Column A will produce a letter. The formulas in Column B will produce a number. An #N/A error will be in only one of the cells in Column B in the specified range. I need a formula in cell C1 that will look in Columns A and B and when it sees the #N/A error in the cell in Column B, it will return the corresponding letter in Column A. So in this example, the formula in cell C1 will produce the result A, because the #N/A error is in cell B6. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array formula SUMIF with 2D sum_range array | Excel Worksheet Functions | |||
Array formula: how to join 2 ranges together to form one array? | Excel Worksheet Functions | |||
Find specific value in array of array formula | Excel Worksheet Functions | |||
meaning of : IF(Switch; Average(array A, array B); array A) | Excel Worksheet Functions | |||
Array Formula - using LEFT("text",4) in formula | Excel Worksheet Functions |