Hi Angela,

When you place a zero in the row or column number arguments for INDEX it will return the whole row/column. Tip, wrap your formual in SUM like this:

=SUM(INDEX(INDIRECT(E$15),$I10,$J10))

and you'll see it adds up the values in row 5 of the Data sheet. 

However since you can't place the results of the whole row in a single cell, it will return the value at the implicit intersection of the cell. In this case, it is returning the value that is in column E of the Data sheet. i.e. the same column that your formula is in.

So, yes this is normal behaviour. If you want it to return the value from column C, then you should input the col_num argument as 1, not zero.

Mynda

Hi Mynda, 

Thanks so much for your quick reply and your advice. 

When I inserted 3 columns in the data worksheet, the 1st column data appears error #value on the presentation worksheet. (I have attached another file for your easy reference.)

Why is it so? INDEX seems to be an unstable formula.

Other than 1 and above for column in INDEX, what are things that I need to look out for in INDEX?

Best regards

Angela

Hi Angela,

Sorry for the slow reply. Your workbook isn't attached. Can you please upload it again?

Mynda

Hi Mynda, 

Sorry, I guess when I uploaded the file earlier it was not attached properly. Hereby I attach the file again. Thanks again Mynda

Best regards

Angela

Since your data range starts in column F, but you have put the formula in column E, there is no implicit intersection and you get a #VALUE error because, as Mynda said earlier, you can't return an entire array to one cell.

Hi Velouria and Mynda,

Thanks for your reply. Actually, my question is  - with the same index formula ie same range with column 0, why in both the above attached files, one appears with a value but the other with #VALUE. It doesn't make sense.

As I am using INDEX formula, the row and column are taken from the external cell. These external cell are in formula and may have return with "0". Hence, I need to know if it returns with a zero "0", the INDEX formula should give me #VALUE and not a VALUE. This will serve as an alert to me.

How can I be sure it will give me a #VALUE, instead of a value?

Best regards

Angela

Hi Angela,

Have you read the article on implicit intersection that Mynda linked to?

Because you are not specifying a column for INDEX to use in it's lookup, it takes the column number of the column that the formula is in on the Presentation sheet - this is implicit intersection. 

So in cell E10 it is actually looking up =INDEX(INDIRECT(E$15),1,5) but in your Data sheet you don't have any data in column 5 which is Col E.

You'll notice that the values in Col F and Col G on the Presentation sheet correspond to the values in Col F and Col G on the Data sheet.

If you delete column C or column D or column E on your Data sheet (they are all empty) you'll see that your formulae work.

Regards

Phil

Dear Philip

Thanks for your reply.

I don't think I would ever understand why the EXCEL developer team would let the column number (in letters) to play a role that will determine the return whether it is a value or an error when the INDEX column is 0. 

Guess I just have to accept that's how it is in EXCEL - implicit intersection.

Thanks so much for your enlightenment.

I have attached a file for those who may have the same question.

Best regards

Angela