excel worksheet to worksheet formula

I used to have a formula that looked at another worksheet where it would look at either a 3,4, or 5 digit number and would give a price out of another column.  Here is the formula

=VLOOKUP(VALUE(LEFT(F2,IF(ISNUMBER(LEFT(F2,5)*1),5,IF(ISNUMBER(LEFT(F2,4)*1),4,IF(ISNUMBER(LEFT(F2,3)*1),3,0))))),'[Grogan''s Price List 10-24-11]Grogan''s Price'!$A$2:$M$1000,13,FALSE)

Now instead of just 3,4, or 5 numbers in the cell it also has letters at the end of the numbers.  Like before it might have 433, it now would be 433JLL.  So the formula has stopped working.  How do I fix this formula to look at not just the numbers but the whole cell?
TomBallaAsked:
Who is Participating?
 
barry houdiniCommented:
It's not clear to me why you would need that complex formula in the first place, if F2 could have been a 3, 4 or 5 digit number then the lookup value is just using that same number, so I'm not sure why you can't just look up F2, i.e.

=VLOOKUP(F2,'[Grogan''s Price List 10-24-11]Grogan''s Price'!$A$2:$M$1000,13,FALSE)

If you are expecting F2 to contain 433JLL and that to match with the same value in column A of the other worksheet then return the value from column M then that should work for you

regards, barry

0
 
barry houdiniCommented:
Hello TomBalla,

you say > Like before it might have 433, it now would be 433JLL

Are you referring to the contents of F2 or the entries in Grogan''s Price'!$A$2:$A$1000 (or both)?

regards, barry
0
 
TomBallaAuthor Commented:
Both will now have numbers and letters.
0
 
TomBallaAuthor Commented:
Ok, that appears to be working.  I can't remember why we had such a convuluted formula before.
Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.