excel worksheet to worksheet formula

Posted on 2011-10-23
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
    LVL 50

    Expert Comment

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

    Both will now have numbers and letters.
    LVL 50

    Accepted Solution

    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

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
    Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
    The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
    This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

    737 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

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now