Link to home
Start Free TrialLog in
Avatar of TomBalla
TomBallaFlag for United States of America

asked on

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?
Avatar of barry houdini
barry houdini
Flag of United Kingdom of Great Britain and Northern Ireland image

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
Avatar of TomBalla

ASKER

Both will now have numbers and letters.
ASKER CERTIFIED SOLUTION
Avatar of barry houdini
barry houdini
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Ok, that appears to be working.  I can't remember why we had such a convuluted formula before.
Thanks.