LOOKUP Function

User avatar
BobH
UraniumLounger
Posts: 9261
Joined: 13 Feb 2010, 01:27
Location: Deep in the Heart of Texas

LOOKUP Function

Post by 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.
Bob's yer Uncle
(1/2)(1+√5)
Dell Intel Core i5 Laptop, 3570K,1.60 GHz, 8 GB RAM, Windows 11 64-bit, LibreOffice,and other bits and bobs

User avatar
HansV
Administrator
Posts: 78370
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: LOOKUP Function

Post by 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?
Best wishes,
Hans

User avatar
BobH
UraniumLounger
Posts: 9261
Joined: 13 Feb 2010, 01:27
Location: Deep in the Heart of Texas

Re: LOOKUP Function

Post by 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.
Bob's yer Uncle
(1/2)(1+√5)
Dell Intel Core i5 Laptop, 3570K,1.60 GHz, 8 GB RAM, Windows 11 64-bit, LibreOffice,and other bits and bobs

User avatar
HansV
Administrator
Posts: 78370
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: LOOKUP Function

Post by 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?
Best wishes,
Hans

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 15576
Joined: 24 Jan 2010, 23:23
Location: brings.slot.perky

Re: LOOKUP Function

Post by ChrisGreaves »

BobH wrote:
24 Dec 2023, 00:26
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
You do not have the required permissions to view the files attached to this post.
There's nothing heavier than an empty water bottle

User avatar
SpeakEasy
4StarLounger
Posts: 544
Joined: 27 Jun 2021, 10:46

Re: LOOKUP Function

Post by 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.

User avatar
BobH
UraniumLounger
Posts: 9261
Joined: 13 Feb 2010, 01:27
Location: Deep in the Heart of Texas

Re: LOOKUP Function

Post by BobH »

Thank you, Gentlemen!
Bob's yer Uncle
(1/2)(1+√5)
Dell Intel Core i5 Laptop, 3570K,1.60 GHz, 8 GB RAM, Windows 11 64-bit, LibreOffice,and other bits and bobs