excel worksheet to worksheet formula
Posted on 2011-10-23
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?