Very mysterious to me that COLUMN() will not work in this case. I illustrate with an example to isolate the problem.
Fill in V1:V10 on Sheet2 dates 9/1/18, 9/2/18...9/10/18.
Enter into W1:W10 numbers: 15, 18, 9, 25, -15, 14, 7, 3, -7, 4.
There is a defined name "firstrow" which is = 1 (but might change).
The following formula looks BACKWARDS in the numeric series and returns the first date value where the criteria in the numeric data is met. In this case the criteria is ">10" so the value that is returned is 9/6/18 which is the first date value that corresponds to meeting the criteria ">10" (going from the end backwards).
The formula is:
=LOOKUP(2,1/($W$1:$W$10>10),$V$1:$V$10) and the result with the given data is 9/6/18.
Now, follow the trail, i change the formula one piece at a time and it always returns the correct result until i introduce the COLUMN() function, where it stops working correctly.
Change to use INDIRECT
=LOOKUP(2,1/(INDIRECT("W1:W10")>10),$V$1:$V$10) value returned: 9/6/18
Now use INDIRECT and OFFSET
=LOOKUP(2,1/(OFFSET(INDIRECT("W1"),0,0,10,1)>10),$V$1:$V$10) value returned: 9/6/18
Now use ADDRESS to generate the INDIRECT reference
=LOOKUP(2,1/(OFFSET(INDIRECT(ADDRESS(firstrow,23,,,"Sheet2")),0,0,10,1)>10),$V$1:$V$10) value returned: 9/6/18 (still correct)
Now, use COLUMN to return 23 instead of hard-coding it in the formula.
=LOOKUP(2,1/(OFFSET(INDIRECT(ADDRESS(firstrow,COLUMN($W$1),,,"Sheet2")),0,0,10,1)>10),$V$1:$V$10)
The value returned is #N/A. (seems WRONG to me).
Can anybody explain what is going on here? COLUMN($W$1) of course returns 23. Shouldn't this work?
I am trying to make this formula more robust, so that if i insert a column the formula will not break. That is why i am trying to use ADDRESS within INDIRECT.
Thank you!
Fill in V1:V10 on Sheet2 dates 9/1/18, 9/2/18...9/10/18.
Enter into W1:W10 numbers: 15, 18, 9, 25, -15, 14, 7, 3, -7, 4.
There is a defined name "firstrow" which is = 1 (but might change).
The following formula looks BACKWARDS in the numeric series and returns the first date value where the criteria in the numeric data is met. In this case the criteria is ">10" so the value that is returned is 9/6/18 which is the first date value that corresponds to meeting the criteria ">10" (going from the end backwards).
The formula is:
=LOOKUP(2,1/($W$1:$W$10>10),$V$1:$V$10) and the result with the given data is 9/6/18.
Now, follow the trail, i change the formula one piece at a time and it always returns the correct result until i introduce the COLUMN() function, where it stops working correctly.
Change to use INDIRECT
=LOOKUP(2,1/(INDIRECT("W1:W10")>10),$V$1:$V$10) value returned: 9/6/18
Now use INDIRECT and OFFSET
=LOOKUP(2,1/(OFFSET(INDIRECT("W1"),0,0,10,1)>10),$V$1:$V$10) value returned: 9/6/18
Now use ADDRESS to generate the INDIRECT reference
=LOOKUP(2,1/(OFFSET(INDIRECT(ADDRESS(firstrow,23,,,"Sheet2")),0,0,10,1)>10),$V$1:$V$10) value returned: 9/6/18 (still correct)
Now, use COLUMN to return 23 instead of hard-coding it in the formula.
=LOOKUP(2,1/(OFFSET(INDIRECT(ADDRESS(firstrow,COLUMN($W$1),,,"Sheet2")),0,0,10,1)>10),$V$1:$V$10)
The value returned is #N/A. (seems WRONG to me).
Can anybody explain what is going on here? COLUMN($W$1) of course returns 23. Shouldn't this work?
I am trying to make this formula more robust, so that if i insert a column the formula will not break. That is why i am trying to use ADDRESS within INDIRECT.
Thank you!
