I am trying to get my head around this.
I have a table that (for some reason unknown to me) the owner writes the date in a String and Numeric format (Sunday 08/11/15). I can't edit this doc, but I need to extract names that correspond with dates from it.
I'm currently trying to use filter to grab a partial match:
=filter('sheet 1'!$A$3:$A$9,search($B$2,'sheet 1'!$A$3:$A$9))
This is ran in Column F, and pulls the contents from Sheet 1 into the current sheet (lets call it sheet 2) if Column B (which uses Today()) date matches. This part seems to be working perfectly fine.
What I then need to do, is match the contents of column F against the names in Sheet 1 that correspond with that date.
Sheet 1 looks like:
| Date | Team IOH | Team OOH | Team 1 IOH | Team 2 OOH |
|------------------|----------|----------|------------|------------|
|Monday 02/11/15| Dave | CTeam | BTeam | BTeam 2 |
|Tuesday 03/11/15| Dave | CTeam | BTeam 2 | BTeam |
|Wednesday 04/11/15| CTeam | CTeam | BTeam | BTeam 2 |
So, the date pulled from Sheet 1 as Wednesday 04/11/15 needs to pull those corresponding teams.
Sheet 2 looks like:
| Team | Date | IOH | OOH | BUTeam | F (Hidden colum) |
|--------|----------|---------|---------|--------|------------------|
| Team | =TODAY() | Dave | CTeam | CTeam |Monday 02/11/15|
| Team 1 | =TODAY() | BTeam | BTeam 2 | CTeam |Monday 02/11/15|
| Team 2 | =TODAY() | BTeam 2 | BTeam | CTeam |Monday 02/11/15|
So if =TODAY() was Monday (for example) it would pull the names from Sheet 1 Team IOH, and palce that in the IOH column of sheet 1. And the same for the following.
Sorry, probably could have explained this better but my brain is frazzled.
See my previous question with some tinkering around, I managed to get the transpose working . Rather than close this, I will answer it and leave it in case anyone has a question around how to find Partial Matches (As I don't see it very often)