CODEWITHSUNDEEP
excelexcel-formula
skkakkar
skkakkar

Reputation: 2828

Logic behind last evaluation step of a LOOKUP formula to find last non empty value in a row.

I want to get last non empty cell from every row for further operations. Searching Net I came across a LOOKUP formula which can do this job fine. I have alpha as well as numeric data in the rows. Sample Data is shown below.

ID1 ID2 ID3 ID4 ID5     ID6 ID7 ID8 ID9 ID10    ID11
a1  a2  a3  a4  a5      23  24  25      25  

23  NN  67  99  200001  ss  p1  23  rq          3

I am using the following formula in L4 which gives correct results that is 3 in L4. Similar formula for row 2 gives result of 25 in L2. To understand the formula I carried out Formula Evaluation but I am not getting the logic of last step as how it translates to final result.

=LOOKUP(2,1/(A4:K4<>""),A4:K4)

LOOKUP(2,{1,1,1,1,1,1,1,1,1,#DIV/0!,1},A4:K4) After this step result that is the last non empty value of Row 4 comes i.e. 3 Can someone explain how this line is finally translating into the result step by step.

Snapshot

Upvotes: 0

Views: 128

Answers (2)

Axel Richter
Axel Richter

Reputation: 61862

This approach finding the last filled cell uses the vector form of LOOKUP. In

=LOOKUP(2,{1,1,1,1,1,1,1,1,1,#DIV/0!,1},A4:K4) the 2 is the lookup_value, the array {1,1,1,1,1,1,1,1,1,#DIV/0!,1} is the lookup_vector and the A4:K4 is the result_vector.

The vector form of LOOKUP looks in a lookup_vector for the lookup_value and returns the value from the same position in the result_vector.

If the LOOKUP function can't find the lookup_value directly in the lookup_vector, the function matches the largest value in lookup_vector that is less than or equal to lookup_value. If multiple equal values are in the array, which are the largest which are less than or equal to lookup_value, the position of the last is taken. In this case it matches the last 1 since this is the largest value in lookup_vector that is less than or equal to 2.

But for this to do, there is the rule, that the values in lookup_vector must be placed in ascending order. And this is the not documented feature with this approach. It seams as if the #DIV/0! errors within lookup_vector does not contradict that rule. But since the source is not open, we can't be absolutely sure about this. But this approach is used sucessfully so often, that we can be very sure.

Upvotes: 2

Steven Martin
Steven Martin

Reputation: 3272

Basically taking the below formula as an example

=LOOKUP(2,1/(A1:D1<>""),A1:D1)

With data in A1:D1 

A1  B1  C1  D1
1    2   3

The formula will populate as follows

=LOOKUP(2,{1,1,1,#DIV/0!},{1,2,3,0})

The way this works is quite simple.

1/(A1:D1<>"") creates an array of 1's and #DIV\0!

            1  where the cell is not blank
            #DIV/0  where the cell is blank

Then the lookup formula looks for a 2 in this array , which it cant find, but it will always return the position of then last NON ERROR value in the context of an array filled with 1's and #DIV/0!, which would be the 3rd position in the array, The LOOKUP formula then returns the 3rd position of the last argument array in the formula A1:D1 , which is 3

Upvotes: 3

Related Questions