LOOKUP Function

[BobH]

How this function works escapes me.

Here is a formula in a spreadsheet: =LOOKUP(1001,$I2:$I32,D2:D32)

The intent is to return the value in column D, a date, when the value in column I, a number, equals (or exceeds) the value 1000. I tried both 1000 and 1001 and get the same result.

The formula returns the column D value as a number (which requires that the cell be formatted as a date) from the row before the column I value equals or exceeds 1001. Column I contains a value less than 1000 in the spreadsheet, but the next row value exceeds 1000.

Can someone please explain this for me? I've read several articles but none shed light on the issue for me.

[HansV]

1) Are the numbers in I2:I32 sorted in ascending order?
2) Have those numbers been entered manually, or are they the result of a formula?

[BobH]

Merry Christmas, Hans!

1) The numbers in column I are not in ascending order.
2) They are the result of a formula.

They represent the month-to-date KWH in my electricity usage spreadsheet. The formula simply enters each new daily value to the previous value. Daily values are entered manually.

[HansV]

I'd expect month-to-date electricity usage to be non-decreasing. Or do you have solar panels that make your net usage negative on some days?

[ChrisGreaves]

They represent the month-to-date KWH in my electricity usage spreadsheet. The formula simply enters each new daily value to the previous value. Daily values are entered manually.

Hi Bob. We may have more in common than (a) age and (b) large state status.

I too track my energy use. I have a workbook into which I key the rightmost two digits of my 5-digit meter reading and the sheet spits out my predicted bill for next month.
(Newfoundland Hydro bills me on the 15th of each month, but I like to pay all my monthly bills on the first of the month, and then relax!)

You are welcome to a copy if you'd like to see how it works.
Cheers, Chris

[SpeakEasy]

>not in ascending order.

Here's what Microsoft say about that ...

Important: The values in lookup_vector must be placed in ascending order: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise, LOOKUP might not return the correct value. Uppercase and lowercase text are equivalent.

[BobH]

Thank you, Gentlemen!