COLUMN() function not working in this case...why?
Results 1 to 2 of 2

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

  1. #1
    Board Regular
    Join Date
    Mar 2011
    Posts
    231
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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.
    =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!

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,113
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

    Default 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?
    Assuming too much and qualifying too much are two faces of the same problem.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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