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.

Quote from royUK

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

Quote from bosco_yip

=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))

Still Not.....

Please check the attached file for the reference.

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

Great.....prepared with few argument's..

Quote from bosco_yip

=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

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.

Quote from bosco_yip

=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)))

Doing well.....Thanks