+ Reply to Thread
Results 1 to 15 of 15

Lookup formula

  1. #1
    Registered User
    Join Date
    08-23-2017
    Location
    lulu
    MS-Off Ver
    2016
    Posts
    57

    Lookup formula

    hi guys,

    i have in worksheet Data columns with startdates;employee names and numbers
    i want in sheet Column K the startdates ordererd from early to late based on day and employee

    see attachment for example

    kind regards
    Attached Files Attached Files

  2. #2
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,935

    Re: Lookup formula

    Try

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


    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
    If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)

  3. #3
    Registered User
    Join Date
    08-23-2017
    Location
    lulu
    MS-Off Ver
    2016
    Posts
    57

    Re: Lookup formula

    HI shukla.ankur281190,

    Thanks but that is not all what i want.
    if i put the formula in cel D4 is gives me the result of C4. And i want only the startdates from C4 of 2-1-2017 and not others if i pull down the formula

    see attachment
    Attached Files Attached Files

  4. #4
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,935

    Re: Lookup formula

    Place your expected result with your sample file

  5. #5
    Registered User
    Join Date
    08-23-2017
    Location
    lulu
    MS-Off Ver
    2016
    Posts
    57

    Re: Lookup formula

    see my earlier post. i have attached my sample file

  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,893

    Re: Lookup formula

    Array formula in B4, copied across and down:
    =IFERROR(INDEX(Data!$K:$K,SMALL(IF(Data!$M$2:$M$1988='Column K'!$B$2,IF(INT(Data!$K$2:$K$1988)='Column K'!B$3,ROW(Data!$M$2:$M$1988))),ROWS(B$4:B4))),"")
    Attached Files Attached Files
    Last edited by Glenn Kennedy; 11-23-2017 at 09:22 AM.
    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
    Registered User
    Join Date
    08-23-2017
    Location
    lulu
    MS-Off Ver
    2016
    Posts
    57

    Re: Lookup formula

    that is awesome Thanks a lot!

  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,893

    Re: Lookup formula

    You're welcome.



    If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

    It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

  9. #9
    Registered User
    Join Date
    08-23-2017
    Location
    lulu
    MS-Off Ver
    2016
    Posts
    57

    Re: Lookup formula

    Hi Glenn Kennedy,

    can you help me with the same formula but tranposed to columns
    See attachments sheet New Columns K
    Attached Files Attached Files

  10. #10
    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,893

    Re: Lookup formula

    Back in an hour....

  11. #11
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Lookup formula

    Try

    =IFERROR(INDEX(Data!$K$2:$K$2000,SMALL(IF((INT(Data!$K$2:$K$2000)=$A3)*(Data!$M$2:$M$2000=$A$1),ROW(Data!$K$2:$K$2000)-ROW($K$2)+1,""),COLUMNS($A:A))),"")

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

  12. #12
    Registered User
    Join Date
    08-23-2017
    Location
    lulu
    MS-Off Ver
    2016
    Posts
    57

    Re: Lookup formula

    Hi John Topley,

    The formula doesn't work.
    could you place the sample file including your formula and dragged down if possible

  13. #13
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Lookup formula

    See attached (assuming I understood requirement)
    Attached Files Attached Files

  14. #14
    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,893

    Re: Lookup formula

    I didn't look at JT's attachment...

    =IFERROR(INDEX(Data!$K:$K,SMALL(IF(INT(Data!$K$2:$K$2000)=$A3,IF(Data!$M$2:$M$2000=$A$1,ROW(Data!$K$2:$K$2000))),COLUMNS($A:A))),"")

    array entered.
    Attached Files Attached Files

  15. #15
    Forum Expert
    Join Date
    12-09-2014
    Location
    Trakai, Lithuania
    MS-Off Ver
    2016
    Posts
    1,278

    Re: Lookup formula

    Apply the formula
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files

+ 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. Replies: 3
    Last Post: 08-23-2017, 07:04 PM
  2. [SOLVED] Modified LOOKUP Formula for Excluding Missing Lookup Values
    By aaochsner in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-23-2017, 07:29 PM
  3. Replies: 4
    Last Post: 05-19-2015, 08:42 PM
  4. [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
  5. [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
  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