They say the mind goes second ....

[David]

One of those weeks!

Cells I1 through J 12 are as below:
Jan 1
Feb 2
Mar 3
Apr 4
May 5
Jun 6
Jul 7
Aug 8
Sep 9
Oct 10
Nov 11
Dec 12

In D1, I have =VLOOKUP(C1;$I$1:$J$12;2)
and copy down column D for 12 cells. I put the months, as Jan, Feb, ... down column C.

Now, here are the results for the changing months in column C:

C D
Jan 1
Feb 2
Mar 3
Apr 4
May 5
Jun 6
Jul 2
Aug #N/A
Sep 9
Oct 8
Nov 8
Dec #N/A

So ...... What has happened after "Jun" ????

David.

[Villeroy]

You left out the 4th param of VLookup, so you get unpredictable results from an unsorted list:
=VLOOKUP(C1;$I$1:$J$12;2;0)
works as espected
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org

[David]

[Villeroy]

No need to sort if you set the last argument to 0 explicitly. The default works like this:
myRange:
0 low
1 medium
2 large
=VLOOKUP(0.9;myRange;2) >> low
=VLOOKUP(1.123;myRange;2) >> medium
=VLOOKUP(1000;myRange;2) >> large
=VLOOKUP(-1;myRange;2) >> #NA (smaller than first value)
IMHO the default, assuming 1 as last argument, should not be the default. But 0 as default would break old spreadsheet traditionals.
MATCH(search;vector;type) works the same way. Additionally MATCH accepts 0,1 and -1 as last argument. -1 indicates a vector sorted in descending order.
0 high
-1 medium
-2 low
=MATCH(-1.5;$A$1:$A$3;-1) >> 2
If you need VLOOKUP on a descending vector:
=INDEX($B$1:$B$3;MATCH(-1.5;$A$1:$A$3;-1)) >> medium
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org

[David]

Thanks, Villeroy. I may not be too old to learn, but I might also be getting too old to concentrate. Too many irons in the fire.

David.