# Index match issues when referencing to dates

#### Ecel Dumbo

##### Member

I am having trouble getting index match formula correct when i'm trying to reference dates and also when looking up values not found in source table

#### Attachments

• 14.9 KB Views: 3

#### rahulshewale1

##### Active Member
hi @Ecel Dumbo,

See if is ok ?

=IFERROR(INDEX(\$D\$7:\$I\$10,MATCH(1,IF(\$B\$7:\$B\$10=D17,IF(\$C\$7:\$C\$10=C17,1)),0),MATCH(B17,\$D\$6:\$I\$6,0)),0)

Regard
Rahul shewale

#### Attachments

• 15.5 KB Views: 2

#### Ecel Dumbo

##### Member
Hi, I tried your formula. But still giving me incorrect answer. I checked the formatting on date cells, they look ok. See file attached

#### Attachments

• 12.6 KB Views: 2

#### GraH - Guido

##### Well-Known Member
Couple of things I observed:
- no need for an array formula, as you are looking for a unique ID in the rows and a unique date in the columns, the INDEX(range,match(),match()) build-up will work without CSE.
- you seem to be looking for exact matches, so use the 3 argument false in the match functions.
- when you put a single quote in front of the data values in the cells B36 and B42 the formula does work.

#### Attachments

• 14.9 KB Views: 3

#### Peter Bartholomew

##### Well-Known Member
As I see it there are two problems to be addressed. The major one is that converting to a table turns the dates into text strings. To get a match you need to convert the search string before using MATCH. I have defined a name 'textDate' for this
=TEXT(date,"dd/mm/yyyy")
The other potential problem is that the final formula needs to be committed as an array with CSE. To avoid this, I again use a named formula. By defining the Boolean 'employee?' to refer to
= IF( (Results[Name]=name) * (Results[Employee ID'#]=ID), 1 )
The array calculation is performed before the worksheet implicit intersection takes place:
= IFERROR( INDEX( achieved, MATCH( 1, employee?, 0 ), MATCH( textDate, dateHdr, 0 ) ), 0 )
My apologies for messing with your formulas to such an extent but getting rid of direct cell referencing is the only way I can read what a formula is meant to be doing.

#### Attachments

• 12.7 KB Views: 2
Last edited:

#### Ecel Dumbo

##### Member
Thank you Grah
THanks heaps Peter for explaining the root cause of my formula problem