+ Reply to Thread
Results 1 to 13 of 13

Lookup first two letters and return the most recent data

  1. #1
    Forum Contributor
    Join Date
    04-20-2011
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2007
    Posts
    121

    Lookup first two letters and return the most recent data

    I am trying to read the first 2 letters of a word and return the most recent match from a column in another work sheet where the first 2 letters are the same.

    Example:
    Cell B1 in the active worksheet contains Bg23

    Column F in sheet 4 has many words beginning with Bg---, the most recent is Bg4 and this is what I need to see. #VALUE is the result.

    Any help appreciated, formula used -

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Chambo1160

  2. #2
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Lookup first two letters and return the most recent data

    Hi,

    I'm a bit confused.

    For a start, you say that it's column F that contains all these values beginning in "Bg", yet your VLOOKUP is currently set to search in column A (yet return the corresponding value from column 6, i.e. F).

    What's more - and the reason you are getting a #VALUE! error - the double-unary (--) is attempting to coerce the string extracted by the LEFT function into a numerical, but clearly "Bg" can never be interpreted as such.

    Can you perhaps post a workbook and clarify what it is you are trying to achieve?

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  3. #3
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,655

    Re: Lookup first two letters and return the most recent data

    Try this Array Formula. Since this is an array formula so you need to confirm it with Ctrl+Shift+Enter instead of just Enter. (i.e. select the cell where you want to place this formula --> press F2 (function key) --> paste the formula given below in the cell --> and then hold down the Ctrl+Shift and then press Enter.)

    Please Login or Register  to view this content.
    Is this what you want?
    Last edited by sktneer; 05-10-2014 at 09:56 AM.
    Regards
    sktneer


    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.

  4. #4
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Lookup first two letters and return the most recent data

    How are you determining "most recent"? Is it done by date, the top of the list, the bottom of the list or some other method?
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  5. #5
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Lookup first two letters and return the most recent data

    @sktneer

    I strongly recommend not using entire columns as references in array formulas. Unlike e.g. SUMIF(S)/COUNTIF(S), they calculate over the entire range specified, whether beyond the "last used" cell in that range or not.

    Regards

  6. #6
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,655

    Re: Lookup first two letters and return the most recent data

    Quote Originally Posted by newdoverman View Post
    How are you determining "most recent"? Is it done by date, the top of the list, the bottom of the list or some other method?
    Good point. I assumed that he is interested in the bottom most value. I may be wrong though.

  7. #7
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,655

    Re: Lookup first two letters and return the most recent data

    Quote Originally Posted by XOR LX View Post
    @sktneer
    I strongly recommend not using entire columns as references in array formulas.
    Regards
    Thanks for pointing this out. I have edited the formula.

  8. #8
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Lookup first two letters and return the most recent data

    This will find the last entry for "Bg" (the first 2 characters of Sheet1!B1) found on Sheet4 in column F if that is what you are looking for.

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  9. #9
    Forum Contributor
    Join Date
    04-20-2011
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2007
    Posts
    121

    Re: Lookup first two letters and return the most recent data

    I could see there were responses yesterday but they were not showing up for some reason.
    First of all thanks to all for the support and apologies for any confusion.
    I attach a spread sheet which will make things clearer.
    Sheet 3 shows in red what I am trying to achieve (comes in from Sheet 4 cells A and D 55-64). Sheet 4 is a list which will continue to grow as and when sub assets are changed.
    Thanks again

    Martin
    Attached Files Attached Files

  10. #10
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,655

    Re: Lookup first two letters and return the most recent data

    Try this.......

    Array Formula in D2 on sheet3. (Refer to post#3 for how to confirm an array formula)
    Please Login or Register  to view this content.
    and then drag down.

    In C2 on sheet3, try this regular formula (not an array formula)

    Please Login or Register  to view this content.
    and then drag down.

    For detail see the attached sheet.
    Attached Files Attached Files

  11. #11
    Forum Contributor
    Join Date
    04-20-2011
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2007
    Posts
    121

    Re: Lookup first two letters and return the most recent data

    Genius. Many many thanks sktneer that works perfectly.

  12. #12
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,655

    Re: Lookup first two letters and return the most recent data

    You're welcome. Thanks for the feedback.
    If that takes care of your question, please mark your thread as solved.
    Moreover you may also click on * (star) to Add Reputation.

  13. #13
    Forum Contributor
    Join Date
    04-20-2011
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2007
    Posts
    121

    Re: Lookup first two letters and return the most recent data

    Good morning sktneer. I set the formula in my spread sheet model which works fine in picking up all of the sub assets from sheet 4 e.g. Bt, Hv, Cmp, etc. If all of the sub asset in dates are the same MAX value then they all show up.

    If I then add a sub asset in at a later date, then only that sub asset is visible, the rest of the sub asset cells go blank. Could the formula be modified so that they all stay visible at their relative latest sub asset in date.

    I can send a further worksheet if needed.

    Thanks again

    Martin

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Return the most recent date from a given cell
    By jrainbow in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-15-2012, 06:03 PM
  2. How to return value that's the most recent?
    By surfol in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-03-2010, 09:15 AM
  3. Return Value with most recent date
    By Kles in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-14-2009, 05:52 PM
  4. How do you return the 5 most recent dates from a list?
    By domcron in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 10-31-2006, 01:57 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1