Vlookup的替代品，它不止于发现的第一个值 [英] Alternative to Vlookup that does not stop at first value found

问题描述

我试图根据以下条件填充B2:D5:是"/否"(图1)，前提是我找到相应的pvalue(列A)，在另一张纸的测试"列中，Fvalue匹配图1的列标题.我尝试使用图3中可见的公式.但是，它错误地标记了母牛和鸡的列.我怀疑是由于它停止在它找到的第一个"True"值，而不是在找到该True值之后对其他值进行迭代.

I am trying to populate B2:D5 with "yes"/"No" ( Figure 1) based on the criteria that if I find the respective pvalue( Column A) , in the 'Test' column in a separate sheet and the Fvalue matches the column header of figure 1. I tried using the formula visible in figure 3. However, it incorrectly labels the cow and chicken columns. Which I suspect is due to it stopping at the first " True" value it finds, and not iterating over the other values once it finds said true value.

推荐答案

使用COUNTIFS()

Use COUNTIFS()

在B2中:

=IF(COUNTIFS(Sheet1!A:A,$A2,Shee1!B:B,B$1),"Yes","No")

然后在网格上方和下方复制.

Then copy over and down the grid.

其中Sheet1是列表.

Where Sheet1 is the list.

这篇关于Vlookup的替代品，它不止于发现的第一个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！