Index Match along with SMALL and COUNTIF

[asad]

Hello Guys,
I got a three formulae that return results from another worksheet. One of these is working alright - the one that returns the dates. But the two other are not working. I am posting a portion here. Any help is appreciated and THANKS in advance.

Excel Workbook
	A	B	C	D
1	LAST NAME	FIRST NAME	DC Exp date	31/12/2012
2	#NUM!	#NUM!	26/03/2008
3	#NUM!	#NUM!	10/07/2008
4	#NUM!	#NUM!	10/07/2008
5	#NUM!	#NUM!	10/07/2008
DC

 

[asad]

OK
I will post another sample where another formula is working but it is giving me the same result for same date in three different cells. I would like to get three right results which will be different person name in all three cases.

Excel Workbook
	A	B	C	D	E	F
1	LAST NAME	FIRST NAME	DC Exp date	31/12/2012
2	#NUM!	#NUM!	26/03/2008			BOGDANOVIC
3	#NUM!	#NUM!	10/07/2008			SIPILIANO
4	#NUM!	#NUM!	10/07/2008			SIPILIANO
5	#NUM!	#NUM!	10/07/2008			SIPILIANO
DC

 

[asad]

I worked it out myself, so no need of any solutions now. Thanks anyway to anyone who may have tried to solve it for me.

 

[T. Valko]

I worked it out myself, so no need of any solutions now. Thanks anyway to anyone who may have tried to solve it for me.

OK, glad you got it worked out. Here's a tip that'll save you a few keystrokes when dealing with complex formulas like those:

=IF(ISERROR(INDEX(Details!$C$2:$R$40,MATCH($C4,Details!R$2:R$40,0),3)),"",SMALL(INDEX(Details!$C$2:$R$40,MATCH($C4,Details!R$2:R$40,0),3),COUNTIF(C$2:C4,C4)))

When error trapping, the portion of the formula that will actually cause an error is the MATCH function so you can leave out the INDEX portion:

=IF(ISERROR(MATCH($C4,Details!R$2:R$40,0)),"",SMALL(INDEX(Details!$C$2:$R$40,MATCH($C4,Details!R$2:R$40,0),3),COUNTIF(C$2:C4,C4)))

Or, since the MATCH function generates an #N/A error:

=IF(ISNA(MATCH($C4,Details!R$2:R$40,0)),"",SMALL(INDEX(Details!$C$2:$R$40,MATCH($C4,Details!R$2:R$40,0),3),COUNTIF(C$2:C4,C4)))

Or,

=IF(COUNTIF(Details!R$2:R$40,$C4),SMALL(INDEX(Details!$C$2:$R$40,MATCH($C4,Details!R$2:R$40,0),3),COUNTIF(C$2:C4,C4)),"")

 

[asad]

Thanks #NAME for your tips. I will try to memorise them for my other formulae as well. Thanks again

 

[T. Valko]

You're welcome!