Index(match frustration

[SFCChase]

I'm using an Index and Match formula to search a spreadsheet for data. Using a name from column A and a training class from row 40, I would like the completion date from the I9:AX36 table to transfer to another cell further down the page. The formula I'm currently using is =IFNA(INDEX($I$9:$AX$36,MATCH($A44,$A$9:$A$36,0),MATCH(I$40,$I$5:$AX$5,0)),"")

The issue that I'm having is that there are spots in the table that do not have data entered (no completion dates). I would like these to be reflected by resulting in a blank cell, but it is defaulting to a date of 01/00/00

Any suggestions would be greatly appreciated.

 

[njimack]

If you simply want the cell to appear empty, change the formatting. Something like dd-mmm;;

 

[SFCChase]

even with a format change it results a 01-JAN for any cell that is blank in data table up above.

 

[KevCarter]

You may need to run it twice to test, no big deal unless it's a huge dataset. Try something like:

IF(INDEX($I$9:$AX$36,MATCH($A44,$A$9:$A$36,0),MATCH(I$40,$I$5:$AX$5,0))="","",INDEX($I$9:$AX$36,MATCH($A44,$A$9:$A$36,0),MATCH(I$40,$I$5:$AX$5,0)))

or

IF(INDEX($I$9:$AX$36,MATCH($A44,$A$9:$A$36,0),MATCH(I$40,$I$5:$AX$5,0))=0,"",INDEX($I$9:$AX$36,MATCH($A44,$A$9:$A$36,0),MATCH(I$40,$I$5:$AX$5,0)))