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

• 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

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

## Files

• Book1.xlsx

(1.03 MB, downloaded 52 times, last: )
• 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.

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

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