Log In
Register
Home
August 28, 2019
Offline
Hi Mynda,
My Index formula is not working properly in the presentation worksheet, of which the figures are extracted from the data worksheet.
By right, when the column is "0", it is out of range and should have returned with an error result. But instead I tried out in 3 sets of column with the same row and "0" column, it returned with different results taken from the same range.
a. Is it a norm?
b. Is it because I am working on 2 different worksheet?
c. What did I do wrong?
Your enlightenment is much appreciated.
Best regards
Angela
July 16, 2010
Offline
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
August 28, 2019
Offline
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
July 16, 2010
Offline
Hi Angela,
Sorry for the slow reply. Your workbook isn't attached. Can you please upload it again?
Mynda
August 28, 2019
Offline
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
Trusted Members
Moderators
November 1, 2018
Offline
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.
August 28, 2019
Offline
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
October 5, 2010
Offline
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
August 28, 2019
Offline
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
1 Guest(s)
