• 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

  • Book2.xlsx
    14.9 KB · Views: 3
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

  • Book2.xlsx
    15.5 KB · Views: 2
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

  • Book2.xlsx
    12.6 KB · Views: 2
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

  • Copy of Book2_index_match_match.xlsx
    14.9 KB · Views: 3
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

  • Two Way Lookup (PB).xlsx
    12.7 KB · Views: 2
Last edited:
Back
Top