Index match issues when referencing to dates

[Ecel Dumbo]

Hi Please see attached file

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

please advise correct formula and what ent wrong in my approach

 

[rahulshewale1]

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

 

[Ecel Dumbo]

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

 

[GraH - Guido]

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.

 

[Peter Bartholomew]

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.

 

[Ecel Dumbo]

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