Index Match return duplicates with an IF

Akashwani

Akashwani

Well-known Member
Joined
Mar 14, 2009
Messages
2,911
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
ABC
1NameStart DateEnd Date
2Ak14/04/201115/04/2011
3Sabi15/04/201117/04/2011
4MrExcel16/04/201118/04/2011
5Ak19/04/201120/04/2011
6Sabi21/04/201124/04/2011
7MrExcel23/04/201127/04/2011
Data


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


Excel Workbook
ABCDEFGHIJKLMNO
1*14-Apr15-Apr16-Apr17-Apr18-Apr19-Apr20-Apr21-Apr22-Apr23-Apr24-Apr25-Apr26-Apr27-Apr
2AkXX***XX*******
3Sabi*XXX***XXXX***
4MrExcel**XXX****XXXXX
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
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Sort by date Sort by votes
Couldn't figure it out with native functions, so I wrote the below UDF:

Excel Workbook
ABCDEF
1*14-Apr15-Apr16-Apr17-Apr18-Apr
2AkXX   
3Sabi XXX 
4MrExcel  XXX
Planner


Code: 
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
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,224,521
Messages
6,179,282
Members
452,902
Latest member
Knuddeluff

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top