VLOOKUP (LEFT) returns result off by one row

houseofharts

New Member
Joined
Jan 20, 2014
Messages
16
Hi all,

Another novice question. I am using the following

Code:
=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.
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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

New Member
Joined
Jan 20, 2014
Messages
16
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?
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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

New Member
Joined
Jan 20, 2014
Messages
16
Thanks all. Through the magic of Google, I ended up stumbling across something that helped. I added a wildcard qualifier to the string

Code:
=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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,156
Messages
5,600,027
Members
414,356
Latest member
death20

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top