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?
=VLOOKUP(LEFT(A5,4),Customers,3,FALSE)
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?
=VLOOKUP(LEFT(A5,4),Customers,3,FALSE)
Do you mean that the strings in the customers range, 3rd column only have 4 digits?
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.
Ooops, I meant 1st col of course . The N/A suggests no value was found? Maybe post a small sample?
LEFT function always returns a text result, try like this to convert to numeric
=VLOOKUP(LEFT(A5,4)+0,Customers,3,FALSE)
Audere est facere
HI - I registered just to say thank you for this post - it fixed exactly what I needed!!!!
EDIT - I meant to thank Daddy Longlegs
That fixed it - is there a way to mark complete?
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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks