Vlookup using first 4 characters

[djblois1]

I am trying to use this formula to do a vlookup using only the first 4 digits of the cell. However it is coming up as N/A. Anyone have any ideas of how to do this?

Please Login or Register  to view this content.

[arthurbr]

Do you mean that the strings in the customers range, 3rd column only have 4 digits?

[djblois1]

For my purposes yes. At my company we have customers (bill to) and each customer may have many ship to addresses. Customers are always 4 digit numbers. Ship-tos on the other hand, precede with the 4 digit number of its parent and then 1 or 2 letters. An example would be Cust#7777 and Ship-to#7777A.

the column that I am doing left on is the ship-to number and the list that I am looking up the information is the Customer list. I want to pull some information that is stored with the customer (the parent) but not stored with the ship-to.

[arthurbr]

Ooops, I meant 1st col of course . The N/A suggests no value was found? Maybe post a small sample?

[daddylonglegs]

LEFT function always returns a text result, try like this to convert to numeric

=VLOOKUP(LEFT(A5,4)+0,Customers,3,FALSE)

[djblois1]

That fixed it - is there a way to mark complete?

[arthurbr]

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

[leopold1117]

HI - I registered just to say thank you for this post - it fixed exactly what I needed!!!!

EDIT - I meant to thank Daddy Longlegs