VLOOKUP returns unwanted/wrong values

[wcollatz]

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?

 

[Bernard Liengme]

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

 

[T. Valko]

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

 

[wcollatz]

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]

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!