Results 1 to 6 of 6

Thread: Index/Match Date Problem

  1. #1
    Neophyte AllyJM's Avatar
    Join Date
    Jun 2015
    Location
    Ayrshire, Scotland
    Posts
    4
    Articles
    0
    Excel Version
    2003,2007

    Index/Match Date Problem



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have a list of friends and family birthdays, and I'm using an Index & Match formula to return the person's name from matching the values of two cells, i.e. Day(A1) and Month(B1). The list of day numbers is in A6:A10, and month numbers in B6:B10, and the names in C6:C10. The formula I'm using is an array formula as follows: {=INDEX(C6:C10,MATCH(A1&B1,A6:A10&B6:B10,0))}. Everything works fine except the one for Jim. His name comes up if I put 11 in cell A1 and 1 in cell B1 his name comes up, which is correct. However, if I swap them and put 1 in cell A1, and 11 in cell B1, his name still comes up, which is incorrect. Is it me or a quirk of Excel? Thanks in advance for any help.


    Can't show a screenshot unfortunately. Haven't posted often enough.
    Attached Thumbnails Attached Thumbnails Click image for larger version. 

Name:	Book1.jpg 
Views:	45 
Size:	93.9 KB 
ID:	3580  

  2. #2
    Administrator Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,721
    Articles
    0
    Excel Version
    2010 on Xubuntu
    Hi
    please post a sample sheet. Pics are useless

  3. #3
    Neophyte AllyJM's Avatar
    Join Date
    Jun 2015
    Location
    Ayrshire, Scotland
    Posts
    4
    Articles
    0
    Excel Version
    2003,2007
    Hope this helps if you get it. Thanks.
    Attached Files Attached Files

  4. #4
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    901
    Articles
    0
    Excel Version
    Excel 2013
    try this forumla
    Code:
    =INDEX(C6:C12;MATCH(1;INDEX((A6:A12=B1)*(B6:B12=A1);0);0))

  5. #5
    Conjurer hossat's Avatar
    Join Date
    May 2015
    Location
    Algiers, Algeria
    Posts
    114
    Articles
    0
    Excel Version
    365
    hi,

    Or just add something between the day and the month. for example "-" :

    {=INDEX(C6:C10,MATCH(A1&"-"&B1,A6:A10&"-"&B6:B10,0))}

    In your formula if A1 =1 and B1=11 then A1&B1 = 111, if A1=11 and B1=1 then A1&B1 = 111 so the result is the same.

    good luck

  6. #6
    Neophyte AllyJM's Avatar
    Join Date
    Jun 2015
    Location
    Ayrshire, Scotland
    Posts
    4
    Articles
    0
    Excel Version
    2003,2007
    Ahh! Now I understand , and it works a treat. Your help is much appreciated. Thank you.

Posting Permissions

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