LinkBack URL
About LinkBacks
Hello,
I have the below formula that, for some reason, is producing an error.
=VLOOKUP(LEFT(R19,6),'Data'!A:B,2,0)
I am using Microsoft Excel 2010.
Any thoughts?
Thanks!
Hello,
I have the below formula that, for some reason, is producing an error.
=VLOOKUP(LEFT(R19,6),'Data'!A:B,2,0)
I am using Microsoft Excel 2010.
Any thoughts?
Thanks!
Last edited by msawyer; 12-07-2012 at 07:24 PM. Reason: Updated version of excel using
Try the following:
=VLOOKUP(LEFT(R19,6),Data!A:B,2,FALSE)
Hope this helps!
Based on the information provided, not really. The formula itself looks OK. So, first question is, what's in cell R19? And, therefore, what are the leftmost six characters? Are they alphabetic or numeric or a combination? What does the data look like in column A of the Data sheet? Is that alphabetic or numeric or a combination? Could there be leading or trailing spaces? Does the cell have a "number" in it but it's formatted as text? What error do you get? Does the value actually exist in column A of the Data sheet?Any thoughts?
So, no ideas, just lots of questions.
Best idea is to upload a sample workbook with some typical data which demonstrates the problem.
Regards, TMS
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Tried changing '0' to 'FALSE'. Still doesn't work. Thanks.
The single quotes are only necessary if the sheet name has spaces in it. It shouldn't make a difference when there are no spaces.
Regards, TMS
Here are the answers to your questions.
Cell R19 contains a "number". Example 123456-123 The leftmost six characters should be 123456.
Cell R19 is formatted as "General"
Column A contains numbers. Example 123456, 987654, 456321.
Column A is formatted as "General"
I checked for leading and trailing spaces but didn't see any. Isn't there a function to remove those from a cell? Not sure what the function is.
The error comes up as #N/A.
The value does indeed exist in column A of the Data sheet.
I've uploaded a sample workbook.
Thanks for the help.
=VLOOKUP(--LEFT(B1,6),Data!A:B,2,0)
LEFT, RIGHT and MID, amongst many functions, produce text strings ... which will not match the numeric value in the lookup table.
The -- coerces the 6 character text string (of numbers) into a numeric value.
Regards, TMS
Thank you Trevor. That seems to work. What is weird is I have the same formula in another excel file (created in version 2007) that works just fine. Is this something new to 2010?
No. it must be something to do with the format of the data in the other Excel file. Perhaps the column is formatted as text, or the values have single quotes.
Basically, the format of the lookup data needs to match the format of the data being searched. If it doesn't, it won't work. Simple as that.
Excel 2010 may have "looked at" your data and thought, "I know, that looks like a number, I'll make it numeric ... just being helpful"That I don't know, I don't have 2010.
Regards, TMS
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Or you can use this way:
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
Thanks for the rep.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks