Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLookup Returning last cell??
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLookup Returning last cell??
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLookup Returning last cell??
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLookup Returning last cell??
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 | |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLookup Returning last cell??
On Nov 1, 2:41 pm, wrote:
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- 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? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLookup Returning last cell??
Look he
http://www.cpearson.com/excel/lookups.htm#ClosestMatch -- Kind regards, Niek Otten Microsoft MVP - Excel wrote in message oups.com... | On Nov 1, 2:41 pm, wrote: | 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- 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? | |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLookup Returning last cell??
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! On Nov 1, 2:52 pm, wrote: 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? |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLookup Returning last cell??
On Nov 1, 3:00 pm, "Niek Otten" wrote:
Look he http://www.cpearson.com/excel/lookups.htm#ClosestMatch -- Kind regards, Niek Otten Microsoft MVP - Excel wrote in ooglegroups.com... | On Nov 1, 2:41 pm, wrote: | 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- 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vlookup returning a value in more than one cell | Excel Worksheet Functions | |||
Vlookup Returning #n/a | Excel Worksheet Functions | |||
vlookup is returning a value one cell above the correct cell. | Excel Worksheet Functions | |||
Leave cell data (IF(ISNA(VLOOKUP) is TRUE instead of returning blank (" ") or #NA | Excel Worksheet Functions | |||
vlookup returning sum | Excel Worksheet Functions |