VLookup Returning last cell??

[it]

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!C640,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

 

[JE McGimpsey]

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.

 

[it]

From XL Help ("VLOOKUP"):


If you're not sorting your values in ascending order, you need to set
Range_lookup to FALSE.








- 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

 

[Niek Otten]

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


| > 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 <[email protected]>,
| >
| >
| >
| > (e-mail address removed) 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!C640,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
|

 

[it]

From XL Help ("VLOOKUP"):

If you're not sorting your values in ascending order, you need to set
Range_lookup to FALSE.


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- Hide quoted text -

- Show quoted text -

I've also noticed:
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.290,A1:B16,2) will return 'd' and I would like it to
return 'e'...the value closest...is there a way?

 

[Sandy Mann]

From Help:

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

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Niek Otten]

Look here:

http://www.cpearson.com/excel/lookups.htm#ClosestMatch

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| On Nov 1, 2:41 pm, (e-mail address removed) 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 <[email protected]>,
| >
| > > (e-mail address removed) 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!C640,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- Hide quoted text -
| >
| > - Show quoted text -
|
| I've also noticed:
| 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.290,A1:B16,2) will return 'd' and I would like it to
| return 'e'...the value closest...is there a way?
|

 

[domenic22]

For the closest value, assuming that A2:B8 contains the data, try the
following formula that needs to be confirmed with CONTROL+SHIFT
+ENTER....

=INDEX(B2:B8,MATCH(MIN(ABS(A2:A8-0.290)),ABS(A2:A8-0.290),0))

Hope this helps!

 

[it]

Look here:

http://www.cpearson.com/excel/lookups.htm#ClosestMatch

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


| On Nov 1, 2:41 pm, (e-mail address removed) 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 <[email protected]>,
| >
| > > (e-mail address removed) 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!C640,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- Hide quoted text -
| >
| > - Show quoted text -
|
| I've also noticed:
| 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.290,A1:B16,2) will return 'd' and I would like it to
| return 'e'...the value closest...is there a way?
|

Thanks to you all for the help! I got it working using the index and
match functions.