将单元格与列表进行比较，如果匹配则在Excel中标记其他列 [英] Compare a cell to a list and if match mark a different column in Excel

问题描述

我对某些单元格格式有疑问.我正在尝试确定单元格列表中是否包含单元格的(非数字)值.如果单元格匹配，请用Y标记其他列，否则用N标记.我的问题与时，它并没有给我正确的结果.我也尝试了=IF(I2='SALT, WOD, Champion Members'!A$2:A$39,"Y", "N")，但是在将公式扩展到第40行之后开始出现错误.

I have a question about some cell formatting. I am trying to identify if a cell's (non-numerical) value is contained in a list of cells. If the cell matches, mark a different column with a Y, otherwise mark it with a N. My question is very similar to the question asked here, but when I use =If(Isnumber(Match(I2,'SALT, WOD, Champion Members'!A$2:A$39,0)),"Y","N"), it doesn't give me correct results. I also tried =IF(I2='SALT, WOD, Champion Members'!A$2:A$39,"Y", "N"), but started giving me errors after I extended the formula past the 40th row.

感谢您的协助！

推荐答案

好的，有几件事需要澄清.

Okay, there are a couple of things that need to be clarified.

首先，格式设置与excel中的外观有关，而Y或N的放置与格式无关.

First, formatting is about how things in excel looks and putting Y or N isn't about formatting.

现在，如果我了解您尝试使用的公式，则有一个名为SALT, WOD, Champion Members的工作表，并且在此工作表的A列中，有许多非数字值的列表，您需要根据这些值检查单元格I2.如果该值在其中，则要获取Y，否则要获取N.

Now, if I understand the formula you tried to use, you have a sheet named SALT, WOD, Champion Members and in this sheet in the column A, there is the list of many non-numerical values you need to check against a value in cell I2. If this value is in there, you want to get Y, otherwise N.

您使用的第一个公式应该是为您寻找单元格A2至A39中列表中任何值的结果，因此您可能会认为这是不正确的.整列也许更安全，这意味着A:A:

The first formula you used should be giving you the results you seek for any value in the list within cells A2 to A39, so this is what you might be considering being incorrect. Maybe it's safer to take the whole column, meaning A:A:

=if(isnumber(match(I2, 'SALT, WOD, Champion Members'!A:A, 0)), "Y", "N")

这篇关于将单元格与列表进行比较，如果匹配则在Excel中标记其他列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！