excel worksheet to worksheet formula

Posted on 2011-10-23
Medium Priority
Last Modified: 2012-05-12
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?
Question by:TomBalla
  • 2
  • 2
LVL 50

Expert Comment

by:barry houdini
ID: 37014872
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

Author Comment

ID: 37014882
Both will now have numbers and letters.
LVL 50

Accepted Solution

barry houdini earned 2000 total points
ID: 37014909
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


Author Comment

ID: 37014937
Ok, that appears to be working.  I can't remember why we had such a convuluted formula before.

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question