+ Reply to Thread
Results 1 to 4 of 4

vlookup to return multiple cells based on month

Hybrid View

  1. 10-19-2013, 02:06 AM #1
    ashwin272012
    ashwin272012 is offline
    Registered User
    Join Date
    10-17-2013
    Location
    hyd
    MS-Off Ver
    Excel 2007
    Posts
    6

    Post vlookup to return multiple cells based on month

    I have a list of holidays in a table. when I enter date in F1 cell it has to return holidays in that month in H12:h15 column. but it is returning only 1st value.
    11/1/2013
    NO DATE HOLIDAY
    1 14-Jan-13 Pongal Nov-2013
    2 26-Jan-33 Republic Day Su Mo Tu We Th Fr Sa
    3 27-Mar-33 Holi 01 02
    4 11-Apr-13 Ugadi 03 04 05 06 07 08 09
    5 9-Aug-13 Id-Ul-Fitr 10 11 12 13 14 15 16
    6 15-Aug-13 Independence Day 17 18 19 20 21 22 23
    7 9-Sep-13 Ganesh Chaturthi 24 25 26 27 28 29 30
    8 13-Oct-13 Vijaya Dashami
    9 14-Oct-13 Vijaya Dashami
    10 3-Nov-13 Diwali
    11 25-Dec-13 Christmas

    need that suppose when I enter any date in aug month it has to retun like this
    9-aug id-ul-fitr
    15-aug independence day
    but it giving only 9-aug id-ul-fitr
    Reply With QuoteRegister To Reply
  2. 10-19-2013, 02:16 AM #2
    RobertMika
    RobertMika is offline
    Forum Expert RobertMika's Avatar
    Join Date
    06-22-2009
    Location
    Haverhill, UK
    MS-Off Ver
    Excel 2003-13
    Posts
    1,530

    Re: vlookup to return multiple cells based on month

    You need something likt this
    http://office.microsoft.com/en-gb/ex...001226038.aspx
    Attached your workbook if you need solution based on your data.
    If you are http://www.excelforum.com/image.php?type=sigpic&userid=125481&dateline=1392355029happy with the results, please add to the contributor's
    reputation by clicking the reputation icon (star icon).



    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved.
    To undo, select Thread Tools-> Mark thread as Unsolved.
    http://www.excelaris.co.uk
    Reply With QuoteRegister To Reply
  3. 10-19-2013, 04:44 AM #3
    kvsrinivasamurthy
    kvsrinivasamurthy is offline
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,710

    Re: vlookup to return multiple cells based on month

    pl see the attached file.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  4. 10-19-2013, 10:57 AM #4
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: vlookup to return multiple cells based on month

    Try this...


    Data Range
    A
    B
    C
    D
    E
    F
    1
    NO
    DATE
    HOLIDAY
    -----
    Date
    Holidays
    2
    1
    14-Jan-13
    Pongal
    1-Aug-13
    Id-Ul-Fitr
    3
    2
    26-Jan-13
    Republic Day
    Independence Day
    4
    3
    27-Mar-13
    Holi
    5
    4
    11-Apr-13
    Ugadi
    6
    5
    9-Aug-13
    Id-Ul-Fitr
    7
    6
    15-Aug-13
    Independence Day
    8
    7
    9-Sep-13
    Ganesh Chaturthi
    9
    8
    13-Oct-13
    Vijaya Dashami
    10
    9
    14-Oct-13
    Biff Day
    11
    10
    3-Nov-13
    Diwali
    12
    11
    25-Dec-13
    Christmas


    E2 = the 1st of the month date for the month of interest.

    Enter this array formula** in F2:

    =IFERROR(INDEX(C:C,SMALL(IF(TEXT(B$2:B$12,"mmmyyyy")=TEXT(E$2,"mmmyyyy"),ROW(B$2:B$12)),ROWS(F$2:F2))),"")

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.

    Copy down until you get blanks.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Return value of the last week of the month based on the year and month
    By stevekho2 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-14-2013, 11:04 AM
  2. VLookup - Return multiple values in multiple cells HELP
    By sreeves1 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-02-2012, 12:58 PM
  3. vlookup formula to return multiple rows of data based on the same value.
    By Point5 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-14-2011, 06:03 PM
  4. Formula to return day based on month end date & day number of month
    By mikeburg in forum Excel General
    Replies: 3
    Last Post: 09-25-2007, 10:26 AM
  5. [SOLVED] Can VLOOKUP return multiple answers based on several identical lo.
    By jddtct in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-11-2005, 03:06 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1