Index/Match Date Problem

[AllyJM]

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.

[Pecoflyer]

Hi
please post a sample sheet. Pics are useless

[AllyJM]

Hope this helps if you get it. Thanks.

[navic]

try this forumla

=INDEX(C6:C12;MATCH(1;INDEX((A6:A12=B1)*(B6:B12=A1);0);0))

[hossat]

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

[AllyJM]

Ahh! Now I understand , and it works a treat. Your help is much appreciated. Thank you.