VLOOKUP (LEFT) returns result off by one row

[houseofharts]

Hi all,

Another novice question. I am using the following

=VLOOKUP((LEFT(C4,6)),'Data from 7500'!$B$16:$G$195,6,TRUE)

and it works great, except that the data returned is off by one row. For example, the correct value for the sample name in B107 is located in G107, but the formula returns the value in cell G106. I've tried changing the TRUE to FALSE and that returns #N/A.

Thanks in advance for any thoughts.

 

[Jonmo1]

What type of value is the Left function retrieving? Is it supposed to be a number?
The Left function returns a Text String, even if it looks like a number

Try
=VLOOKUP(LEFT(C4,6)+0,'Data from 7500'!$B$16:$G$195,6,TRUE)

Also, the data must be sorted Ascending by column B in B16:G195

 

[houseofharts]

Thanks, Jonmo1. The LEFT function is retrieving data that is numbers formatted as text. Specifically, it is an identifier like 10-21, 10-22, 10-23, etc and the entire column is formatted as text to keep excel from trying to read it in date format.

I already had the data range sorted by ascending. I tried changing the code to what you suggested above, and it returned NA. Any thoughts?

 

[Aladin Akyurek]

Does changing from TRUE to FALSE or 0 help?

 

[Jonmo1]

if the strings are 10-21 10-22 etc, that is 5 characters, but you're using 6 in the left function.

Try
=VLOOKUP(LEFT(C4,5),'Data from 7500'!$B$16:$G$195,6,TRUE)

 

[houseofharts]

Thanks all. Through the magic of Google, I ended up stumbling across something that helped. I added a wildcard qualifier to the string

=VLOOKUP(LEFT(C2,6)&"*",'Data from 7500'!$B$16:$G$195,6,FALSE)

and that solved the problem. Yes, the example values are five digits, but it's possible a user could have values of 10-100, 10-101, etc so I was trying to allow for the maximum digits possible. If you see any potential problems with this, I'm all ears. For now, it seems to work fine.

Thanks again.