Index Match return duplicates with an IF

[Akashwani]

Hi,

I'm trying to help Sabi here.....
http://www.mrexcel.com/forum/showthread.php?t=544826
and I'm totally baffled as to how to resolve this. I've tried Index, Match, Row, Column, Countif and other combinations all to no conclusion, HELP

The lookup table is something like this....

Excel Workbook
	A	B	C
1	Name	Start Date	End Date
2	Ak	14/04/2011	15/04/2011
3	Sabi	15/04/2011	17/04/2011
4	MrExcel	16/04/2011	18/04/2011
5	Ak	19/04/2011	20/04/2011
6	Sabi	21/04/2011	24/04/2011
7	MrExcel	23/04/2011	27/04/2011
Data

The data when returned should be something like this.....

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	*	14-Apr	15-Apr	16-Apr	17-Apr	18-Apr	19-Apr	20-Apr	21-Apr	22-Apr	23-Apr	24-Apr	25-Apr	26-Apr	27-Apr
2	Ak	X	X	*	*	*	X	X	*	*	*	*	*	*	*
3	Sabi	*	X	X	X	*	*	*	X	X	X	X	*	*	*
4	MrExcel	*	*	X	X	X	*	*	*	*	X	X	X	X	X
Planner

As you can see the formula in B2 when copied across and down will only put in an X for the first instance of the name. How can I expand/alter this part of the formula....
=IF(AND(B$1>=Data!$B2,B$1<=Data!$C2),"X","")


Any suggestions?

Thanks

Ak

 

[njimack]

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1		14-Apr	15-Apr	16-Apr	17-Apr	18-Apr	19-Apr	20-Apr	21-Apr	22-Apr	23-Apr	24-Apr	25-Apr	26-Apr	26-Apr
2	Ak	X	X				X	X
3	Sabi		X		X				X			X
4	MrExcel			X		X					X
Planner

 

[njimack]

no thats not it, i'll try again

 

[njimack]

Couldn't figure it out with native functions, so I wrote the below UDF:

Excel Workbook
	A	B	C	D	E	F
1	*	14-Apr	15-Apr	16-Apr	17-Apr	18-Apr
2	Ak	X	X
3	Sabi		X	X	X
4	MrExcel			X	X	X
Planner

Function Date_Check(Nm As String, Dt As Date)

Dim Last_Row As Long
Dim i As Long
Dim s As String

Date_Check = ""

With Sheets("Data")
    Last_Row = .Range("A" & Rows.Count).End(xlUp).Row
    
    For i = 2 To Last_Row
        If .Cells(i, 1) = Nm Then
            If .Cells(i, 2) <= Dt And .Cells(i, 3) >= Dt Then
                Date_Check = "X"
            Exit Function
            End If
        End If
    Next i
End With


End Function

 

[Akashwani]

Hi Neil,

Wow, thank you so much for your solution and your previous formula. Your contribution has been greatly appreciated and I will keep your UDF for future reference. I'm sure Sabi will be equally as grateful.

Thanks again

Ak