Last non-empty cell in the column based on three header rows that are criteria

  • Hi,

    I would like to find a way to write a formula that will return the value of the last non-empty cell in the column. I have three header rows that are criteria.


    MATCH(Display!$I$3&Summary!$M$8&Summary!$M$6,INDEX(JCB!$C$1:$CB$1&JCB!$C$2:$CB$2&JCB!$C$3:$CB$3,0),0)


    This formula gives the position of the desire column. however ,need to be retrieve the value of the last non-empty cell in this column.


    Thanks in advance for any/all advise.

  • Try this


    =LOOKUP(2,1/(MATCH(Display!$I$3&Summary!$M$8&Summary!$M$6,INDEX(JCB!$C$1:$CB$1&JCB!$C$2:$CB$2&JCB!$C$3:$CB$3,0),0)<>""),MATCH(Display!$I$3&Summary!$M$8&Summary!$M$6,INDEX(JCB!$C$1:$CB$1&JCB!$C$2:$CB$2&JCB!$C$3:$CB$3,0),0))

  • =LOOKUP(2,1/(MATCH(Display!$I$3&Summary!$M$8&Summary!$M$6,INDEX(JCB!$C$1:$CB$1&JCB!$C$2:$CB$2&JCB!$C$3:$CB$3,0),0)<>""),MATCH(Display!$I$3&Summary!$M$8&Summary!$M$6,INDEX(JCB!$C$1:$CB$1&JCB!$C$2:$CB$2&JCB!$C$3:$CB$3,0),0))

    Hi royUK,


    This is also returning column number only. i am looking for the value of the last non-empty cell in this column

  • This formula also gives the desire result, but I want to do it without fixing any range in the column...... like.... JCB! B:B


    =OFFSET(JCB!$B$5,MATCH(9.99999999999999E+307,JCB!B:B)-5,MATCH(Display!$I$3&Summary!$M$8&Summary!$M$6,INDEX(JCB!$C$1:$CB$1&JCB!$C$2:$CB$2&JCB!$C$3:$CB$3,0),0))


    any other solution please..

  • Maybe try,


    =LOOKUP(9^9,COLUMN(JCB!$C$1:$CB$1)-COLUMN(JCB!$C$1)+1/(Display!$I$3&Summary!$M$8&Summary!$M$6=JCB!$C$1:$CB$1&JCB!$C$2:$CB$2&JCB!$C$3:$CB$3))

  • The above formula return the position of the desire column


    and this formula return the last non-empty cell value in the range of JCB!$C$5:$CB$5 :


    =LOOKUP(9^9,JCB!$C$5:$CB$5/(Display!$I$3&Summary!$M$8&Summary!$M$6=JCB!$C$1:$CB$1&JCB!$C$2:$CB$2&JCB!$C$3:$CB$3))


    Regards

  • Based on your attached file.


    The revised formula :


    =LOOKUP(9^9,OFFSET(INDEX(JCB!1:1,INDEX(MATCH(Display!$I$3&Summary!$M$8&Summary!$M$6,JCB!1:1&JCB!2:2&JCB!3:3,0),0)),,,60000))

  • Very Perfect ...Thnx:)


    one more quick question ......How can I get offset value....

    e.g. This formula is extracting value from the cell number AP1134... how can i get it from the third offset column

    that is cell number AR1134

  • Very Perfect ...Thnx:)


    one more quick question ......How can I get offset value....

    e.g. This formula is extracting value from the cell number AP1134... how can i get it from the third offset column

    that is cell number AR1134

    Just add 2 in the OFFSET 3rd argument, and the formula become >>


    =LOOKUP(9^9,OFFSET(INDEX(JCB!1:1,INDEX(MATCH(Display!$I$3&Summary!$M$8&Summary!$M$6,JCB!1:1&JCB!2:2&JCB!3:3,0),0)),,2,60000))

  • =LOOKUP(9^9,OFFSET(INDEX(JCB!1:1,INDEX(MATCH(Display!$I$3&Summary!$M$8&Summary!$M$6,JCB!1:1&JCB!2:2&JCB!3:3,0),0)),,2,60000))

    Hi,


    This formula is extracting value from the cell number AP1134. However , by using same formula , I was again struggling to extract one more value from the second column cell (B1134)


    which is present in the same row (AP1134)


    =OFFSET(AP1134,,COLUMN(AP1134)*-1+2,,)

    this formula is doing well . But how can I apply to below formula


    LOOKUP(9^9,OFFSET(INDEX(JCB!1:1,INDEX(MATCH(Display!$I$3&Summary!$M$8&Summary!$M$6,JCB!1:1&JCB!2:2&JCB!3:3,0),0)),,2,60000))


    Thank You

  • .. to extract one more value from the second column cell (B1134)..........................

    To extract B1134 date value in Offset from the cell number AP1134 :


    =LOOKUP(9^9,OFFSET(INDEX(JCB!1:1,INDEX(MATCH(Display!$I$3&Summary!$M$8&Summary!$M$6,JCB!1:1&JCB!2:2&JCB!3:3,0),0)),,2-INDEX(MATCH(Display!$I$3&Summary!$M$8&Summary!$M$6,JCB!1:1&JCB!2:2&JCB!3:3,0),0),60000))

  • If the Date column is dragged down, this formula will extract the last cell content. In fact, I want the date value in the same row.

    Or, try to use INDIRECT function :


    =INDIRECT("JCB!B"&MATCH(9^9,OFFSET(INDEX(JCB!1:1,INDEX(MATCH(Display!$I$3&Summary!$M$8&Summary!$M$6,JCB!1:1&JCB!2:2&JCB!3:3,0),0)),,,60000)))

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!