From Excel HELP:

"If lookup_value is smaller than the smallest value in the first column of table_array, VLOOKUP returns the #N/A error value."

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


wrote in message ups.com...
| On Nov 1, 10:52 am, JE McGimpsey wrote:
| From XL Help ("VLOOKUP"):
|
| Range_lookup is a logical value that specifies whether you want
| VLOOKUP to find an exact match or an approximate match. If TRUE or
| omitted, an approximate match is returned. In other words, if an
| exact match is not found, the next largest value that is less than
| lookup_value is returned. If FALSE, VLOOKUP will find an exact match.
| If one is not found, the error value #N/A is returned.
|
| and
|
| If range_lookup is TRUE, the values in the first column of
| table_array must be placed in ascending order: ..., -2, -1, 0, 1, 2,
| ..., A-Z, FALSE, TRUE; otherwise VLOOKUP may not give the correct
| value. If range_lookup is FALSE, table_array does not need to be
| sorted.
|
| If you're not sorting your values in ascending order, you need to set
| Range_lookup to FALSE.
|
| In article .com,
|
|
|
| wrote:
| Here is the list I am using from worksheet 1 (named
| "charges")...partial list...worksheet is 400 rows long
| COLUMN C COLUMN D
| 22.942777778 18
| 22.993611111 19
| 23.054166667 20
| 23.149444444 21
| ...
| 13.403611111 262
| 13.469722222 263
| 13.534722222 263
| 13.687500000 263
|
| Here is the list I am using from worksheet 2
| COLUMN B COLUMN C
| 23:00 19
| 23:05
|
| I'm using this formula from worksheet 2 in column C
| =VLOOKUP((B28-0)*24,Charges!C6:D40,2)
|
| Everything works fine when I use the above formula...but when I expand
| the query set to D400 (because there may be up to 400 rows of data) it
| always returns the last cell (263) with data.
|
| Any idea why it returns the last row? Is there a max in the range?
| Any help is very much appreciated.
|
| -BD- Hide quoted text -
|
| - Show quoted text -
|
| I sorted and it works 99.9 % but I have found something wierd. Below
| is my list:
|
| 0.014722 a
| 0.071111 b
| 0.134722 c
| 0.201389 d
| 0.294722 e
| 0.337500 f
| 0.395556 g
|
| if I use the following vlookup it will return NA...why?
| =VLOOKUP(0.013,A1:B16,2)
|
| -BD
|