如果日期匹配获取数据 [英] If dates match get data

问题描述

我有两个独立的Google表格文件。一个带有日期和名称列表（我们称之为Doc1），另一个带有名称和联系人号码列表（我们称之为Doc2）。以下是我想要做的：

在Doc2中。从今天的日期取出与Doc1相对应的名称。然后从Doc2中取出联系人号码，并将其放在名称旁边。

听起来很简单，但我试图用vlookup做到这一点无济于事，想知道在这里有人能指点我的方向吗？

为了澄清，Doc1看起来像这样：

  | Day |日期|团队1 |团队2 |团队3 | 
 | --------------- | --------------- | -------- | ---- ---- | -------- | 
 |星期一| 2015年3月2日|鲍勃|亚当|戴夫| 
 |星期二| 2015年3月3日|约翰|詹姆斯|迈克| 
 |星期三| 4 Mar 2015 | Philip | Dan | John | 
  

Doc2看起来像这样：

  |团队|今日日期|名称|联络号码| 
 | --------------- | --------------- | -------- | ---- -------- | 
 |团队1 | 03/03/2015 | | | 
 |团队2 | 03/03/2015 | | | 
 |团队3 | 03/03/2015 | | | 
  

联系电话号码在Doc2的其他页面上列出，名称旁边。
所以，我试图弄清楚的是，在Doc2中，如何使今天的日期与Doc1中的相应日期相匹配，并将名称从Doc1拖到Doc2中，然后从Doc2中取出对应于现在被拉的名字。

编辑

看起来像，准确。列字母与我正在使用的工作表中的列表完全一样：E，H，K，N，Q，T，W，Z，AC，AF，AI，AL，AO，AR用作分隔符。尴尬，我知道。但是有人做了原始表，我无法编辑它。

pnuts脚本工作正常，并且返回了名字Bob，但是为它下面的团队返回了Initials在Hit类别中（在这个例子中，BB）。对于之后的每个条目（保存下一个返回空白的条目），它将返回REF＃，错误信息为：函数INDEX参数3的值为5.有效值介于0和4之间。 5的价值一直到15（一共有14支球队，加上标题，我猜它可以达到15）。

  ABCDEFGHIJ 
 | Day |日期|团队1 |点击|团队2 |点击|团队3 |点击| 
 | ----------- | --------------- | -------- | ------ |  - ------- | ------ | -------- | ------ | 
 |星期一| 2 Mar 2015 | Bob | BB | Adam | AD | Dave | DV | 
 |星期二| 2015年3月3日| John | JN | James | JM | Mike | MK | 
 |星期三| 4 Mar 2015 | Philip | PH | Dan | DN | John | JN | 
  

解决方案

布局和不同的文档无助于假设 Day 位于工作表 S1 中 Doc1 在 Doc2 的 A1 中的c>和团队，请尝试在 C1 下复制以符合（UNTESTED）：



  =转置（指数（IMPORTRANGE（ <键> 中， S1 B1：电子！），索引（匹配（B2，IMPORTRANGE（ <键> 中，：），0）+1） S1 B2 b！ ，row（）））
  

您必须提供相应的键值并授权链接。

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   |

解决方案

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.

这篇关于如果日期匹配获取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！