+ Reply to Thread
Results 1 to 13 of 13

Lookup formula help please

  1. #1
    Forum Contributor
    Join Date
    11-03-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    246

    Lookup formula help please

    Hi,

    I am using this to find the last date in the range.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    I don't wont to use (N:N) as there are several formulas in column N
    I need to lookup 12 ranges. I tried (N5:N43,N60:N98) but I get an error for to arguments for this function.
    Any help greatly appreciated.

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Lookup formula help please

    An example workbook would make this easier (Go Advanced>Manage Attachments)

    but we can play 20 questions.
    So you have twelve unique ranges in column N which contain dates. They are separated by headers or blanks or both?
    Are the dates within each range in chronological order?
    Are there any other numbers in column N besides dates?
    Do you just need to pull the date or do you need to know where it came from?
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Forum Contributor
    Join Date
    11-03-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    246

    Re: Lookup formula help please

    So you have twelve unique ranges in column N which contain dates. Yes, 12 ranges represent 12 months, dates run 1 to 31 for each range.
    They are separated by headers or blanks or both? Cells NOT to include contain dates calculated by formulas.
    Are the dates within each range in chronological order? Yes
    Are there any other numbers in column N besides dates? Other than the formulas no.
    Do you just need to pull the date or do you need to know where it came from? I just need the last date.
    Many thanks

  4. #4
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Lookup formula help please

    Try this and see if it works
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  5. #5
    Forum Contributor
    Join Date
    11-03-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    246

    Re: Lookup formula help please

    Please see attached, hope this makes it clearer!!
    Attached Files Attached Files

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Lookup formula help please

    In your sample sheet, some for the ranges are 9 cells long, others are 10. Your text suggested athat they were ALL 31 cells deep. Which is it? Please amend your attachment to show EXACTLY what your layout is.
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  7. #7
    Forum Contributor
    Join Date
    11-03-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    246

    Re: Lookup formula help please

    The sheet is just a sample, the ranges will be 39 rows each .

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Lookup formula help please

    Please post an amended and REPRESENTATIVE sheet. How many cells between each range? How many cells with formulae that you need to ignore? Is it the range above, or below the target cell that is being examined?

    I don't enjoy guessing games...

  9. #9
    Forum Contributor
    Join Date
    11-03-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    246

    Re: Lookup formula help please

    See updated file
    Attached Files Attached Files

  10. #10
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Lookup formula help please

    Hi,

    Perhaps this
    =LOOKUP(2,1/(N5:N648<>"")/((MOD(ROW(N5:N648),55)+1)<40),(N5:N648))
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  11. #11
    Forum Contributor
    Join Date
    11-03-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    246

    Re: Lookup formula help please

    Thank you xlnitwit that works perfectly. Thanks to everyone else for your help too.

  12. #12
    Forum Contributor
    Join Date
    11-03-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    246

    Re: Lookup formula help please

    Hi,

    Unfortunately xlnitwit the formula doesn't work after further testing.
    Please see attached file.
    Attached Files Attached Files

  13. #13
    Forum Contributor
    Join Date
    11-03-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    246

    Re: Lookup formula help please

    I have changed the code
    =LOOKUP(2,1/(N5:N648<>"")/((MOD(ROW(N5:N648),55)+1)<40),(N5:N648))
    change <40 to <45
    =LOOKUP(2,1/(N5:N648<>"")/((MOD(ROW(N5:N648),55)+1)<45),(N5:N648))
    This now seems ok.

+ 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. Multi criteria lookup with the ''=lookup' formula
    By Ronnet2 in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 09-17-2015, 07:12 AM
  2. Replies: 4
    Last Post: 05-19-2015, 08:42 PM
  3. [SOLVED] Lookup formula to find lowest value with variable lookup criteria
    By brharrii in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-27-2015, 03:00 AM
  4. [SOLVED] Can lookup formula ignore blank cells until the lookup value has been entered?
    By Ju1cy in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 06-13-2014, 01:32 PM
  5. Create a lookup formula with an If or Lookup function
    By afountas21 in forum Excel General
    Replies: 2
    Last Post: 09-05-2012, 06:53 PM
  6. Replies: 5
    Last Post: 02-24-2011, 11:26 AM
  7. Replies: 3
    Last Post: 10-10-2005, 01:05 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