CODEWITHSUNDEEP
excelrownonblank
Stephen Harper
Stephen Harper

Reputation: 51

How to find second to last non blank value in a row excel

I have data in Y:AI, and return the last non blank value in AJ

L0  L1  L2  L3  L4  L5  L6  L7  L8  L9  L10 Last Code
GP                                          GP
GP  CF  TF                                  CF
GP  CF  TF                                  TF
GP  CF  EX                                  EX
GP  CF  EX  EX1                             EX1
GP  CF  EX  EX2                             EX2
GP  CF  DG                                  DG
GP  CF  DG  DGA                             DGA
GP  CF  DG  DGB                             DGB
GP  CF  DG  DGC                             DGC
GP  CF  DG  DGD                             DGD
GP  CF  DG  DGE                             DGE

I return the last value in AJ3 for example, by using: 

=LOOKUP(2,1/(Y3:AI3<>0),Y3:AI3)

I have tried two different ways to return the second to last non blank value.

=LOOKUP(2,1/(Y3:AI3<>AK3),Y3:AI3)
=LOOKUP(2,1/(or(Y3:AI3<>0, Y3:AI3<>AJ3),Y3:AI3)

The first case returns the value in AI, the second case returns either the value in Y, or a #DIV0! error.

Any help in this would be greatly appreciated.

Upvotes: 2

Views: 5453

Answers (1)

Stephen Harper
Stephen Harper

Reputation: 51

An offline friend managed to help me out!

The second case was a poor attempt at ensuring the cells aren't blank.

The correct way of doing this was:

=LOOKUP(2,1/((Y3:AI3<>AJ3)*(Y3:AI3<>"")),Y3:AI3)

All solved now! :)

Upvotes: 3

Related Questions