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

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

<tbody>
</tbody>

 

[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.