blocks|key|1310454|text|新的Excel版本|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1310455|=IF(ISNA(VLOOKUP(A1,B,B,1,FALSE)),"",A1)|offset|length|style|CODE|1310456|旧版本的Excel|1310457|=IF(ISNA(VLOOKUP(A1;B:B;1;FALSE));"";A1)|1310458|即：“如果B列中存在A1的值，则将其显示在此处；如果不存在，则保留为空。”|1310459|entityMap^0|0|0|14|0|0|0|14|0|0^^$0|@$1|2|3|4|5|6|7|P|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|Q|8|@$D|R|E|S|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|T|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|U|8|@$D|V|E|W|F|G]]|9|@]|A|$]]|$1|L|3|M|5|6|7|X|8|@]|9|@]|A|$]]|$1|N|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]]|O|$]]

New Excel versions

<code>=IF(ISNA(VLOOKUP(A1,B,B,1,FALSE)),"",A1)</code>

Older Excel versions

<code>=IF(ISNA(VLOOKUP(A1;B:B;1;FALSE));"";A1)</code>

That is: "If the value of A1 exists in the B column, display it here. If it doesn't exist, leave it empty."

blocks|key|1310492|text|您尝试过高级筛选器吗？使用短列表作为“条件”，使用长列表作为“列表范围”。使用选项：“原地过滤”和“唯一值”。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1310493|您应该看到仅出现在您的短列表中的唯一值列表。|1310494|或者，如果您愿意，也可以将唯一列表粘贴到其他位置(在同一张工作表上)。选择“复制到另一个位置”选项，然后在“复制到”框中输入您想要唯一列表的单元格引用(例如F1)。|1310495|注意:如果您同时选择这两列作为'Criteria‘和'List+Range’，则这也适用于这两列(名称/ID)。|1310496|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|J|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|K|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|L|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|M|8|@]|9|@]|A|$]]|$1|H|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|I|$]]

Have you tried Advanced Filter? Using your short list as the 'Criteria' and long list as the 'List Range'. Use the options: 'Filter in Place' and 'Unique Values'.

You should be presented with the list of unique values that only appear in your short list.

Alternatively, you can paste your Unique list to another location (on the same sheet), if you prefer. Choose the option 'Copy to another Location' and in the 'Copy to' box enter the cell reference (say F1) where you want the Unique list.

Note: this will work with the two columns (name/ID) too, if you select the two columns as both 'Criteria' and 'List Range'.

blocks|key|1310543|text|我不能让第一种方法工作，我知道这是一个古老的话题，但这是我最终为解决方案所做的：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1310544|=IF(ISNA(MATCH(A1,B:B,0)),"Not+Matched",+A1)|code-block|syntax|javascript|1310545|基本上，将A1与B列完全匹配(0表示与B列中的值完全匹配)。ISNA测试#N/A响应，如果未找到匹配项，则返回匹配项。最后，如果ISNA为true，则将"Not+Matched“写入选定的单元格，否则写入匹配的单元格的内容。|1310546|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

I couldn't get the first method to work, and I know this is an old topic, but this is what I ended up doing for a solution:

<pre><code>=IF(ISNA(MATCH(A1,B:B,0)),"Not Matched", A1)
</code></pre>

Basically, MATCH A1 to Column B exactly (the 0 stands for match exactly to a value in Column B). ISNA tests for #N/A response which match will return if the no match is found. Finally, if ISNA is true, write "Not Matched" to the selected cell, otherwise write the contents of the matched cell.

blocks|key|1525649|text|和其他人一样，我一直对此深恶痛绝。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1525650|我使用了标准；|1525651|=countif(matchingList,C2)=0|offset|length|style|CODE|1525652|其中matchingList是我用作筛选器的列表。|1525653|看看这个|1525654|http://www.youtube.com/watch?v=x47VFMhRLnM&list=PL63A7644FE57C97F4&index=30|1525655|我发现的诀窍是，通常情况下，条件中的列标题与数据列标题相匹配。这不适用于作为公式的条件。|1525656|我发现，如果我将列标题留空，则只有在高级筛选器中具有countif公式的条件有效。如果我在公式示例中有列标题，即列C2的列标题，那么过滤器不会返回任何输出。|1525657|希望这能有所帮助|1525658|entityMap|0|LINK|mutability|MUTABLE|url^0|0|0|0|R|0|0|0|0|23|0|0|0|Q|7|0|0^^$0|@$1|2|3|4|5|6|7|12|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|13|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|14|8|@$F|15|G|16|H|I]]|9|@]|A|$]]|$1|J|3|K|5|6|7|17|8|@]|9|@]|A|$]]|$1|L|3|M|5|6|7|18|8|@]|9|@]|A|$]]|$1|N|3|O|5|6|7|19|8|@]|9|@$F|1A|G|1B|1|1C]]|A|$]]|$1|P|3|Q|5|6|7|1D|8|@]|9|@]|A|$]]|$1|R|3|S|5|6|7|1E|8|@$F|1F|G|1G|H|I]]|9|@]|A|$]]|$1|T|3|U|5|6|7|1H|8|@]|9|@]|A|$]]|$1|V|3|-4|5|6|7|1I|8|@]|9|@]|A|$]]]|W|$X|$5|Y|Z|10|A|$11|O]]]]

I have been hasseling with that as other folks have.

I used the criteria;

<code>=countif(matchingList,C2)=0</code>

where matchingList is the list that i am using as a filter.

have a look at this

<a href="http://www.youtube.com/watch?v=x47VFMhRLnM&amp;list=PL63A7644FE57C97F4&amp;index=30" rel="nofollow">http://www.youtube.com/watch?v=x47VFMhRLnM&amp;list=PL63A7644FE57C97F4&amp;index=30</a>

The trick i found is that normally you would have the column heading in the criteria matching the data column heading. this will not work for criteria that is a formula.

What I found was if I left the column heading blank for only the criteria that has the <code>countif</code> formula in the advanced filter works. If I have the column heading i.e. the column heading for column C2 in my formula example then the filter return no output.

Hope this helps

I have an Excel worksheet with two columns (name/ID) and then another list that is a subset of the names only from the larger aforementioned list. I want to go through the subset list and then pull the data from the larger list (name/ID) and put it somewhere else...essentially only grabbing the data from the larger list if the name is on the subset.

I tried using Filters, but couldn't get it work. Thoughts?

Thanks.

Excel - extracting data based on another list

我有一个包含两列(名称/ID)的Excel工作表，另一个列表是前面提到的较大列表中名称的子集。我想遍历子集列表，然后从较大的列表(名称/ID)中提取数据，并将其放在某个位置，如果名称在子集上，则只从较大的列表中获取数据。我试着使用过滤器，但不能让它工作。有什么想法？谢谢。

问Excel -基于另一个列表提取数据
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Excel -基于另一个列表提取数据EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Excel -基于另一个列表提取数据
EN