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

• 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

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