TomBalla
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(L EFT(F2,5)* 1),5,IF(IS NUMBER(LEF T(F2,4)*1) ,4,IF(ISNU MBER(LEFT( F2,3)*1),3 ,0))))),'[ Grogan''s Price List 10-24-11]Grogan''s Price'!$A$2:$M$1000,13,FAL SE)
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?
=VLOOKUP(VALUE(LEFT(F2,IF(
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?
ASKER
Both will now have numbers and letters.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ok, that appears to be working. I can't remember why we had such a convuluted formula before.
Thanks.
Thanks.
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