I need a formula to help me look up multiple entries in a spreadsheet when I search. Here is the scenario:
I have a list of dates, names, and notes on job performance.
I want to be able to select an employee's name from a drop down (can do that easily in data validation) and then have excel find the list of dates and incidents linked to that employee's entry. The sheet looks like this:
Date Name Notes
1/1/01 John Smith lkjasdlfkjadslkfj
1/1/01 Steve Smith lkjsadlfkjasdklfj
1/2/01 John Smith lkasjdlfkajsdlfkj
I want the function to be able to return (below) if I enter John Smith into a cell like this ----->
Employee name: Incident dates Notes
John Smith 1/1/01 lkjasdlfkjadslkfj
1/2/01 lkasjdlfkajsdlfkj
I have come across something like this in Excel:
=IF(ROWS($F$5:F5)<=COUNTIF($A$5:$A$15,$G$4),INDEX($B$5:$B$14,AGGREGATE(15,3,(($A$5:$A$14=$G$4)/($A$5:$A$14=$G$4)*ROW($A$5:$A$14))-ROW($A$4), ROWS($F$5:F5))),"")
If that is the way to go I will work through it- but I am not an Excel guru and can't really follow that formula...
HELP!
Bookmarks