• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Index match issues when referencing to dates

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
 

Attachments

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

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

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

Last edited:
Top