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]

Need to isolate which (if not both) of the Match functions is returning #N/A

Put the 2 matches into seperate cells
=MATCH(C3,$B$10:$B$21,0)
and
=MATCH(C1,$C$9:$L$9,0)

Which one (or both) returns #N/A ?

 

[barham]

They both return the #N/A. I think I have to use both Matches in order to find the value but do not understand why it returns an error.

 

[Jonmo1]

The only reason for MATCH to return N/A is because a match wasn't found...

Sounds like 1 of your tables is actually Dates, but formatted as either mmmm or dd, while the other is just text strings "January" or numbers 10 11 12 ect..

Take the first match
=MATCH(C3,$B$10:$B$21,0)

What EXACTLY is in C3, a TEXT string "January", or a DATE formatted as "mmmm" ?
Whichever it is, the values in B10:B21 are the opposite.

Same for the 2nd match
=MATCH(C1,$C$9:$L$9,0)

What exactly is in C1, a Date formatted as dd to show only the number, or just an actual number 10 11 12 ?
Whichver it is, the values in C9:L9 are the opposite.

 

[barham]

For C3, I used the date 2/19/15 and the Formula =Text(C2,"mmmm") and used the =text(c2,"D") for to come up with the 19 in C1.

 

[Jonmo1]

OK, that's half of it..

How did the January February values get into B10:B20, and the Day values into C9:L9 ?

 

[barham]

Those values are from a linked pivot table.

 

[Jonmo1]

What I mean is, what are their actual values.
My guess is that they are DATES like 1/1/2015, but the cells are only formatted to show mmmm and/or d

Let's take the 2 matches one at a time.

Take the first match
=MATCH(C3,$B$10:$B$21,0)
Try changing that to
=MATCH(C2,$B$10:$B$21)

What do you get?

 

[barham]

For the purpose of my table, I cannot reference 2/19/15 but instead 19, 20, 21 etc because the table changes daily.

The january is in General and the figures are in numbers. Sorry for the confusion.

 

[barham]

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