find and return last cell in column with data (ignoring those with no data inbetween)

[Flick558]

Hi,
I need to write a conditional formula that will look up in a 2nd worksheet the last coloumn with data (note that there will be some coloumns in the row that will have data and some that will be blank, I need the last column for that specific row to be the return result)

Thanks!

 

[Aladin Akyurek]

Thanks! that seems to work, is there a way if all cells are blank that the formula can return the result "no record of issue"

Wrap it into an IFERROR call...

=IFERROR(LOOKUP(9.99999999999999E+307,1/(1-(INDEX(Sheet1!C:E,MATCH(A2,Sheet1!A:A,0),0)="")),INDEX(Sheet1!C:E,MATCH(A2,Sheet1!A:A,0),0)),"no record of issue")

 

[Flick558]

Wrap it into an IFERROR call...

=IFERROR(LOOKUP(9.99999999999999E+307,1/(1-(INDEX(Sheet1!C:E,MATCH(A2,Sheet1!A:A,0),0)="")),INDEX(Sheet1!C:E,MATCH(A2,Sheet1!A:A,0),0)),"no record of issue")

Thanks so much Aladin! think that's perfect now

 

[Aladin Akyurek]

Thanks so much Aladin! think that's perfect now

You are welcome. Thanks for providing feedback.

 

[Flick558]

Hi Aladin, I'd like to add another component to this spreadsheet, I would like to add another cell with a formula to return the title of the column with the last cell of data thanks!

 

[Aladin Akyurek]

Hi Aladin, I'd like to add another component to this spreadsheet, I would like to add another cell with a formula to return the title of the column with the last cell of data thanks!

I suppose you mean...

D2, copied down:

=IFERROR(LOOKUP(9.99999999999999E+307,1/(1-(INDEX(Sheet1!C:E,MATCH(A2,Sheet1!A:A,0),0)="")),
  Sheet1!$C$1:$E$1),"no record of issue")