# Thread: COLUMN() function not working in this case...why? Thanks: 0 Likes: 0

1. ## COLUMN() function not working in this case...why?

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.
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!  Reply With Quote

2. ## Re: COLUMN() function not working in this case...why?

Merging together OFFSET, INDIRECT, ADDRESS, each of which a volatile function is not a good idea for reasons of efficiency.

Care to describe what you want to achieve in words?  Reply With Quote

## User Tag List

#### Tags for this Thread

9/6/18, column, formula, indirect, returned #### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•