Alternative to Vlookup that does not stop at first

问题:

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.

回答1:

Use COUNTIFS()

In B2:

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

Then copy over and down the grid.

Where Sheet1 is the list.