如何在Excel公式中接受9个首字符? [英] How to accept 9 first characters in excel formula?

问题描述

我试图得到这种结果，我在sheet1上检查是否在sheet2的A列的任何位置找到A列的前三个数字.如果找到数字，则在B列上excel打印是，是否找不到excel打印否:

I am trying to get this sort of result, where I check on sheet1 if the first three numbers on column A are found anywhere in the sheet2's column A. If the numbers are found then on column B excel prints Yes and if it is not found excel prints No:

第1张纸

    A         B  
    =12300    Yes
    =56700    Yes
    =89200    No

第2张纸

    A
    =12345
    =56789
    =44498

我目前在sheet1 col B上的公式是

The formula that I have at the moment on sheet1 col B is

=IF(ISNUMBER(MATCH(A1;Sheet2!A:A));"Yes";"No")

如何添加可能性来检查是否仅考虑了两张纸中的前3个字符?

How to add the possibility to check that it takes into consideration only the 3 first characters in both sheets?

推荐答案

或者，您也可以使用SUMPRODUCT:

=IF(SUMPRODUCT(--(LEFT(A1,3)=LEFT(Sheet2!$A$1:$A$3,3))),"Yes","No")

这篇关于如何在Excel公式中接受9个首字符?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！