Index, Match expert help needed.

[barham]

I used the below formula to get an exact match but it keeps returning a #N/A result:

=INDEX($C$10:$L$21,MATCH(C3,$B$10:$B$21,0),MATCH(C1,$C$9:$L$9,0))

Based on the information on table 1 and 2, I need a value on cell C4. In looking at the table the answer should be 15 but the formula is not returning a valid number. Please help.

Thank you

[TABLE="width: 961"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Columns[/TD]
[TD]Table 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Rows[/TD]
[TD]1[/TD]
[TD]Day of month[/TD]
[TD]19[/TD]
[TD]20[/TD]
[TD]21[/TD]
[TD]22[/TD]
[TD]23[/TD]
[TD]24[/TD]
[TD]25[/TD]
[TD]26[/TD]
[TD]27[/TD]
[TD]28[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2[/TD]
[TD]Date[/TD]
[TD]2/19/2015[/TD]
[TD]2/20/2015[/TD]
[TD]2/21/2015[/TD]
[TD]2/22/2015[/TD]
[TD]2/23/2015[/TD]
[TD]2/24/2015[/TD]
[TD]2/25/2015[/TD]
[TD]2/26/2015[/TD]
[TD]2/27/2015[/TD]
[TD]2/28/2015[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]3[/TD]
[TD]Month[/TD]
[TD]February[/TD]
[TD]February[/TD]
[TD]February[/TD]
[TD]February[/TD]
[TD]February[/TD]
[TD]February[/TD]
[TD]February[/TD]
[TD]February[/TD]
[TD]February[/TD]
[TD]February[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]4[/TD]
[TD][/TD]
[TD]#N/A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]7[/TD]
[TD]Table 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]9[/TD]
[TD]Day of Month[/TD]
[TD]19[/TD]
[TD]20[/TD]
[TD]21[/TD]
[TD]22[/TD]
[TD]23[/TD]
[TD]24[/TD]
[TD]25[/TD]
[TD]26[/TD]
[TD]27[/TD]
[TD]28[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]10[/TD]
[TD]January[/TD]
[TD]9[/TD]
[TD]11[/TD]
[TD]13[/TD]
[TD]5[/TD]
[TD]8[/TD]
[TD]16[/TD]
[TD]11[/TD]
[TD]15[/TD]
[TD]15[/TD]
[TD]12[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]11[/TD]
[TD]February[/TD]
[TD]15[/TD]
[TD]15[/TD]
[TD]15[/TD]
[TD]8[/TD]
[TD]8[/TD]
[TD]18[/TD]
[TD]14[/TD]
[TD]15[/TD]
[TD]21[/TD]
[TD]17[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]12[/TD]
[TD]March[/TD]
[TD]15[/TD]
[TD]15[/TD]
[TD]16[/TD]
[TD]8[/TD]
[TD]8[/TD]
[TD]18[/TD]
[TD]14[/TD]
[TD]15[/TD]
[TD]21[/TD]
[TD]17[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]13[/TD]
[TD]April[/TD]
[TD]20[/TD]
[TD]14[/TD]
[TD]16[/TD]
[TD]9[/TD]
[TD]7[/TD]
[TD]24[/TD]
[TD]19[/TD]
[TD]20[/TD]
[TD]19[/TD]
[TD]18[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]14[/TD]
[TD]May[/TD]
[TD]25[/TD]
[TD]14[/TD]
[TD]13[/TD]
[TD]13[/TD]
[TD]11[/TD]
[TD]19[/TD]
[TD]19[/TD]
[TD]28[/TD]
[TD]22[/TD]
[TD]14[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]15[/TD]
[TD]June[/TD]
[TD]32[/TD]
[TD]18[/TD]
[TD]17[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]21[/TD]
[TD]15[/TD]
[TD]32[/TD]
[TD]25[/TD]
[TD]19[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]16[/TD]
[TD]July[/TD]
[TD]25[/TD]
[TD]17[/TD]
[TD]29[/TD]
[TD]8[/TD]
[TD]8[/TD]
[TD]30[/TD]
[TD]23[/TD]
[TD]25[/TD]
[TD]23[/TD]
[TD]23[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]17[/TD]
[TD]August[/TD]
[TD]28[/TD]
[TD]15[/TD]
[TD]26[/TD]
[TD]14[/TD]
[TD]11[/TD]
[TD]25[/TD]
[TD]24[/TD]
[TD]28[/TD]
[TD]20[/TD]
[TD]29[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]18[/TD]
[TD]September[/TD]
[TD]35[/TD]
[TD]18[/TD]
[TD]21[/TD]
[TD]14[/TD]
[TD]12[/TD]
[TD]21[/TD]
[TD]23[/TD]
[TD]42[/TD]
[TD]26[/TD]
[TD]24[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]19[/TD]
[TD]October[/TD]
[TD]33[/TD]
[TD]22[/TD]
[TD]22[/TD]
[TD]11[/TD]
[TD]17[/TD]
[TD]33[/TD]
[TD]24[/TD]
[TD]32[/TD]
[TD]31[/TD]
[TD]25[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]20[/TD]
[TD]November[/TD]
[TD]29[/TD]
[TD]29[/TD]
[TD]29[/TD]
[TD]15[/TD]
[TD]15[/TD]
[TD]34[/TD]
[TD]27[/TD]
[TD]29[/TD]
[TD]41[/TD]
[TD]33[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]21[/TD]
[TD]December[/TD]
[TD]41[/TD]
[TD]22[/TD]
[TD]25[/TD]
[TD]17[/TD]
[TD]14[/TD]
[TD]25[/TD]
[TD]35[/TD]
[TD]41[/TD]
[TD]30[/TD]
[TD]28[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Jonmo1]

If the values in C9 to L9 are just numbers, 19 20 etc..

Then this should fix the 2nd match
MATCH(C1+0,$C$9:$L$9,0)

Because =text(c2,"D") is returning a TEXT string, even if it looks like a number, excel considers it TEXT so it won't match to the numbers.


Now for this match
=MATCH(C3,$B$10:$B$21,0)
You're saying C3 is =Text(C2,"mmmm")
And the values in B10 to B21 are formatted as General, and showing January February etc..

I can't imagine why that would be #N/A
Except to check for EXACT spelling, and extra spaces in B10 to B21
" January" or "January "

 

[tangswim]

The problem may the format of date of month on row 1 are text but the date of month on row 9 are number. Please try this for date on row1

=value(text(c2,"d")

 

[Jonmo1]

If you don't mind, Providing me an email and I can send you the spreadsheet of what I'm actually dealing with. Thanks

I sent you a PM

 

[barham]

Using MATCH(C1+0,$C$9:$L$9,0) did the trick. I am forever grateful to you.

Thank you in abundance.

 

[Jonmo1]

Using MATCH(C1+0,$C$9:$L$9,0) did the trick. I am forever grateful to you.

Thank you in abundance.

Glad to help, thanks for the feedback.

However, I'd suggest making the correction in the formula for C1 (and filled right)

and used the =text(c2,"D") for to come up with the 19 in C1.

Change that to
=TEXT(C2,"D")+0

 

[Jonmo1]

Also, didn't you say BOTH match functions returned #N/A ?

They both return the #N/A.