CODEWITHSUNDEEP
excel
The Main Man
The Main Man

Reputation: 2433

How does this formula return the last non-zero value in a range

I have an Excel workbook with 1, 4, 0, 100, 0, 0 distributed across the range A1 to A6.

A formula =LOOKUP(2,1/(A1:A6<>0),A1:A6) returns me the last non-zero element in that range: 100 in this case. It's clever enough to step over the 0 between 4 and 100.

It's not my workbook and can't reach the author.

But how does the formula work?

Upvotes: 0

Views: 1152

Answers (1)

Scott Craner
Scott Craner

Reputation: 152450

First, the 2 can be any number greater than 1 and work.

The (A1:A6<>0) is returning TRUE/FALSE which when being used as a denominator turns to 1/0 respectively.

So the 1/(A1:A6<>0) returns an array of 1s and #DIV0 error. For your instance: {1,1,#DIV0,1,#DIV0,#DIV0}

The Lookup looks for best fit, as in where the 2 is greater than the value in the array but less than the next or the last number in the array. So it finds the last 1 in the returned array.

It then uses that relative position 4 to return the forth value in the third criterion range: the value in A4 in this case.

Upvotes: 4

Related Questions