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.
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.
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
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
HI - I registered just to say thank you for this post - it fixed exactly what I needed!!!!
EDIT - I meant to thank Daddy Longlegs
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks