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.
LOOKUP Function
-
- UraniumLounger
- Posts: 9300
- Joined: 13 Feb 2010, 01:27
- Location: Deep in the Heart of Texas
LOOKUP Function
Bob's yer Uncle
Dell Intel Core i5 Laptop, 3570K,1.60 GHz, 8 GB RAM, Windows 11 64-bit, LibreOffice,and other bits and bobs
(1/2)(1+√5) |
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: LOOKUP Function
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?
2) Have those numbers been entered manually, or are they the result of a formula?
Best wishes,
Hans
Hans
-
- UraniumLounger
- Posts: 9300
- Joined: 13 Feb 2010, 01:27
- Location: Deep in the Heart of Texas
Re: LOOKUP Function
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.
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
Dell Intel Core i5 Laptop, 3570K,1.60 GHz, 8 GB RAM, Windows 11 64-bit, LibreOffice,and other bits and bobs
(1/2)(1+√5) |
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: LOOKUP Function
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
Hans
-
- PlutoniumLounger
- Posts: 15655
- Joined: 24 Jan 2010, 23:23
- Location: brings.slot.perky
Re: LOOKUP Function
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.
He who plants a seed, plants life.
-
- 4StarLounger
- Posts: 565
- Joined: 27 Jun 2021, 10:46
Re: LOOKUP Function
>not in ascending order.
Here's what Microsoft say about that ...
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.
-
- UraniumLounger
- Posts: 9300
- Joined: 13 Feb 2010, 01:27
- Location: Deep in the Heart of Texas
Re: LOOKUP Function
Thank you, Gentlemen!
Bob's yer Uncle
Dell Intel Core i5 Laptop, 3570K,1.60 GHz, 8 GB RAM, Windows 11 64-bit, LibreOffice,and other bits and bobs
(1/2)(1+√5) |