| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP returns unwanted/wrong values
I'm using the VLOOKUP function to return data from a named range which
contains several data points (across the row) for several "sites" which are numbered down the left in column A: 26e, 28e,...64e,64w,62w,60w... etc. SAMPLE: Row/Sec. 1 2 3 4 5... Profile Depth (cm) 0.5 10.5 20.5 30.5 40.5... 26e 1.639885 21.143383 30.020775 27.024311 24.509054... -2.5 7.5 17.5 27.5 37.5... 28e 15.430596 21.295532 25.333948 30.626244... 1 11 21 31 41... 30e 0.393868 10.764123 19.071701 15.003836 11.230032... 6.5 16.5 26.5 36.5... 32e 23.005785 21.448273 22.900213 19.946169... The code I am using in another area of the sheet to return these numbers and perform averages on them is: =VLOOKUP("26e",profiledataeast,2,TRUE) where profile dataeast is the named range for the top half of the range that contains data for all east locations and data for the west locations is in the bottom half of the range and I lookup the data with: =VLOOKUP("26w",profiledatawest,2,TRUE) The trouble I am having is, regardless of the designation "e" or "w" in my alphanumeric lookup column on the left, Excel is returning the same data (from the top half "e") to the right for both entries in the column that contain a given number . This can be seen where the new data is displayed after VLOOKUP gets it from "64e" and "64w": 64e 2.087324 19.023703 17.246429 13.043617 16.659203 21.909708 64w 2.087324 19.023703 17.246429 13.043617 16.659203 21.909708 The data returned is data from 64e, but gets returned for =VLOOKUP("64w",profiledatawest,2,TRUE) too! What am I overlooking? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
VLOOKUP returns unwanted/wrong values
You need to simplify the problem so we can see the trees for the forest.
For a start, to ask the question, you do not need all those decimal places Tell use that ranges each name refers to Then we can concentrate on the problem and not the data best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "wcollatz" wrote in message ... I'm using the VLOOKUP function to return data from a named range which contains several data points (across the row) for several "sites" which are numbered down the left in column A: 26e, 28e,...64e,64w,62w,60w... etc. SAMPLE: Row/Sec. 1 2 3 4 5... Profile Depth (cm) 0.5 10.5 20.5 30.5 40.5... 26e 1.639885 21.143383 30.020775 27.024311 24.509054... -2.5 7.5 17.5 27.5 37.5... 28e 15.430596 21.295532 25.333948 30.626244... 1 11 21 31 41... 30e 0.393868 10.764123 19.071701 15.003836 11.230032... 6.5 16.5 26.5 36.5... 32e 23.005785 21.448273 22.900213 19.946169... The code I am using in another area of the sheet to return these numbers and perform averages on them is: =VLOOKUP("26e",profiledataeast,2,TRUE) where profile dataeast is the named range for the top half of the range that contains data for all east locations and data for the west locations is in the bottom half of the range and I lookup the data with: =VLOOKUP("26w",profiledatawest,2,TRUE) The trouble I am having is, regardless of the designation "e" or "w" in my alphanumeric lookup column on the left, Excel is returning the same data (from the top half "e") to the right for both entries in the column that contain a given number . This can be seen where the new data is displayed after VLOOKUP gets it from "64e" and "64w": 64e 2.087324 19.023703 17.246429 13.043617 16.659203 21.909708 64w 2.087324 19.023703 17.246429 13.043617 16.659203 21.909708 The data returned is data from 64e, but gets returned for =VLOOKUP("64w",profiledatawest,2,TRUE) too! What am I overlooking? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
VLOOKUP returns unwanted/wrong values
Try replacing the TRUE argument with either FALSE or 0:
=VLOOKUP("26e",profiledataeast,2,0) Using TRUE, the first column of the table would need to be sorted in ascending order like this: 26e 26w 32e 32w 44e 44w Not like this: 26e 32e 44e 26w 32w 44w -- Biff Microsoft Excel MVP "wcollatz" wrote in message ... I'm using the VLOOKUP function to return data from a named range which contains several data points (across the row) for several "sites" which are numbered down the left in column A: 26e, 28e,...64e,64w,62w,60w... etc. SAMPLE: Row/Sec. 1 2 3 4 5... Profile Depth (cm) 0.5 10.5 20.5 30.5 40.5... 26e 1.639885 21.143383 30.020775 27.024311 24.509054... -2.5 7.5 17.5 27.5 37.5... 28e 15.430596 21.295532 25.333948 30.626244... 1 11 21 31 41... 30e 0.393868 10.764123 19.071701 15.003836 11.230032... 6.5 16.5 26.5 36.5... 32e 23.005785 21.448273 22.900213 19.946169... The code I am using in another area of the sheet to return these numbers and perform averages on them is: =VLOOKUP("26e",profiledataeast,2,TRUE) where profile dataeast is the named range for the top half of the range that contains data for all east locations and data for the west locations is in the bottom half of the range and I lookup the data with: =VLOOKUP("26w",profiledatawest,2,TRUE) The trouble I am having is, regardless of the designation "e" or "w" in my alphanumeric lookup column on the left, Excel is returning the same data (from the top half "e") to the right for both entries in the column that contain a given number . This can be seen where the new data is displayed after VLOOKUP gets it from "64e" and "64w": 64e 2.087324 19.023703 17.246429 13.043617 16.659203 21.909708 64w 2.087324 19.023703 17.246429 13.043617 16.659203 21.909708 The data returned is data from 64e, but gets returned for =VLOOKUP("64w",profiledatawest,2,TRUE) too! What am I overlooking? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
VLOOKUP returns unwanted/wrong values
Thanks T. Valko.
Replacing the true argument with 0 corrected the problem. I had previously tried "FALSE" but that didn't seem to work. I'm not sure why or what the difference between 0 and FALSE would be in this case. Thanks again. "T. Valko" wrote: Try replacing the TRUE argument with either FALSE or 0: =VLOOKUP("26e",profiledataeast,2,0) Using TRUE, the first column of the table would need to be sorted in ascending order like this: 26e 26w 32e 32w 44e 44w Not like this: 26e 32e 44e 26w 32w 44w -- Biff Microsoft Excel MVP "wcollatz" wrote in message ... I'm using the VLOOKUP function to return data from a named range which contains several data points (across the row) for several "sites" which are numbered down the left in column A: 26e, 28e,...64e,64w,62w,60w... etc. SAMPLE: Row/Sec. 1 2 3 4 5... Profile Depth (cm) 0.5 10.5 20.5 30.5 40.5... 26e 1.639885 21.143383 30.020775 27.024311 24.509054... -2.5 7.5 17.5 27.5 37.5... 28e 15.430596 21.295532 25.333948 30.626244... 1 11 21 31 41... 30e 0.393868 10.764123 19.071701 15.003836 11.230032... 6.5 16.5 26.5 36.5... 32e 23.005785 21.448273 22.900213 19.946169... The code I am using in another area of the sheet to return these numbers and perform averages on them is: =VLOOKUP("26e",profiledataeast,2,TRUE) where profile dataeast is the named range for the top half of the range that contains data for all east locations and data for the west locations is in the bottom half of the range and I lookup the data with: =VLOOKUP("26w",profiledatawest,2,TRUE) The trouble I am having is, regardless of the designation "e" or "w" in my alphanumeric lookup column on the left, Excel is returning the same data (from the top half "e") to the right for both entries in the column that contain a given number . This can be seen where the new data is displayed after VLOOKUP gets it from "64e" and "64w": 64e 2.087324 19.023703 17.246429 13.043617 16.659203 21.909708 64w 2.087324 19.023703 17.246429 13.043617 16.659203 21.909708 The data returned is data from 64e, but gets returned for =VLOOKUP("64w",profiledatawest,2,TRUE) too! What am I overlooking? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
VLOOKUP returns unwanted/wrong values
I'm not sure why or what the difference between
0 and FALSE would be in this case. There is no difference other than typing 0 takes fewer keystrokes! To VLOOKUP they're identical and mean exactly the same thing. So, if the formula is working that's all that counts. Thanks for the feedback! -- Biff Microsoft Excel MVP "wcollatz" wrote in message ... Thanks T. Valko. Replacing the true argument with 0 corrected the problem. I had previously tried "FALSE" but that didn't seem to work. I'm not sure why or what the difference between 0 and FALSE would be in this case. Thanks again. "T. Valko" wrote: Try replacing the TRUE argument with either FALSE or 0: =VLOOKUP("26e",profiledataeast,2,0) Using TRUE, the first column of the table would need to be sorted in ascending order like this: 26e 26w 32e 32w 44e 44w Not like this: 26e 32e 44e 26w 32w 44w -- Biff Microsoft Excel MVP "wcollatz" wrote in message ... I'm using the VLOOKUP function to return data from a named range which contains several data points (across the row) for several "sites" which are numbered down the left in column A: 26e, 28e,...64e,64w,62w,60w... etc. SAMPLE: Row/Sec. 1 2 3 4 5... Profile Depth (cm) 0.5 10.5 20.5 30.5 40.5... 26e 1.639885 21.143383 30.020775 27.024311 24.509054... -2.5 7.5 17.5 27.5 37.5... 28e 15.430596 21.295532 25.333948 30.626244... 1 11 21 31 41... 30e 0.393868 10.764123 19.071701 15.003836 11.230032... 6.5 16.5 26.5 36.5... 32e 23.005785 21.448273 22.900213 19.946169... The code I am using in another area of the sheet to return these numbers and perform averages on them is: =VLOOKUP("26e",profiledataeast,2,TRUE) where profile dataeast is the named range for the top half of the range that contains data for all east locations and data for the west locations is in the bottom half of the range and I lookup the data with: =VLOOKUP("26w",profiledatawest,2,TRUE) The trouble I am having is, regardless of the designation "e" or "w" in my alphanumeric lookup column on the left, Excel is returning the same data (from the top half "e") to the right for both entries in the column that contain a given number . This can be seen where the new data is displayed after VLOOKUP gets it from "64e" and "64w": 64e 2.087324 19.023703 17.246429 13.043617 16.659203 21.909708 64w 2.087324 19.023703 17.246429 13.043617 16.659203 21.909708 The data returned is data from 64e, but gets returned for =VLOOKUP("64w",profiledatawest,2,TRUE) too! What am I overlooking? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| vlookup returns wrong answer (off by one row) | Excel Worksheet Functions | |||
| VLOOKUP Formula Returns the Wrong Results. | Excel Worksheet Functions | |||
| Scenario returns wrong values | Excel Discussion (Misc queries) | |||
| Vlookup Returns Wrong/No Data | Excel Worksheet Functions | |||
| Row() function returns wrong row and more.. | Excel Worksheet Functions | |||
Linear Mode
