If dates match get data

问题:

I have two separate GoogleSheets documents. One with a list of dates and names (let's call this Doc1), the other with a list of names and contact numbers (we'll call this Doc2). Here's what I'm trying to do:

In Doc2. Pull the name from Doc1 corresponding with today's date. Then pull the contact number from Doc2 and put it next to the name.

Sounds simple enough, but I've tried to do this with vlookup to no avail, and was wondering if someone here could point me in the right direction?

For clarification, Doc1 looks something like:

| Day           | Date          | Team 1 | Team 2 | Team 3 |
|---------------|---------------|--------|--------|--------|
|Monday         | 2 Mar 2015    |Bob     |Adam    |Dave    |
|Tuesday        | 3 Mar 2015    |John    |James   |Mike    |
|Wednesday      | 4 Mar 2015    |Philip  |Dan     |John    |

Doc2 looks something like:

| Team          | Todays Date   | Name   | Contact No |
|---------------|---------------|--------|------------|
|Team 1         | 03/03/2015    |        |            |
|Team 2         | 03/03/2015    |        |            |
|Team 3         | 03/03/2015    |        |            |

The contact numbers are listed on other pages in Doc2, next to the names. So, what I'm trying to figure out is how in Doc2, to match today's date against the row with corresponding date in Doc1 and pull the name from Doc1 into Doc2, then pull the number from Doc2 that corresponds with the name that has now been pulled.

EDIT

This is what the sheet looks like, accurately. The column letters are listed exactly as they are on the sheet I am working with: E,H,K,N,Q,T,W,Z,AC,AF,AI,AL,AO,AR being used as dividers. Awkward, I know. But someone else made the original sheet and I can't edit it.

pnuts script worked, and returned the first name Bob, but for the team underneath it returned the Initials in the Hit category (in the example, BB). And for every entry after that (save the very next one, which returned blank) it returns REF# with the error: "Function INDEX parameter 3 value is 5. Valid values are between 0 and 4 inclusive." with the value of 5 goign all the way upto 15. (There are 14 teams, plus the title, which I guess makes it upto 15)

  A           B               C        D    E F        G    H I        J  
| Day       | Date          | Team 1 | Hit  | Team 2 | Hit  | Team 3 | Hit  |
|-----------|---------------|--------|------|--------|------|--------|------|
|Monday     | 2 Mar 2015    |Bob     | BB   |Adam    | AD   |Dave    | DV   |
|Tuesday    | 3 Mar 2015    |John    | JN   |James   | JM   |Mike    | MK   |
|Wednesday  | 4 Mar 2015    |Philip  | PH   |Dan     | DN   |John    | JN   |

回答1:

The layouts and different documents don't help but perhaps would get you started, assuming Day is in sheet S1 of Doc1 and Team in A1 of Doc2, please try in C1 and copy down to suit (UNTESTED):

=transpose(index(importrange("<key>","S1!b1:e"),index(match(B2,importrange("<key>","S1!b2:b"),0)+1),row()))  

You would have to provide the appropriate key value and authorise the link.