使用VLOOKUP和IMPORTRANGE查找匹配项并返回两个潜在值之一 [英] Using VLOOKUP and IMPORTRANGE to find a match and return one of two potential values

问题描述

我试图更改单元格B1的值，具体取决于是否在另一个电子表格中找到了单元格A1的值.如果有匹配项，我希望该单元格说禁止".如果在另一个电子表格中未找到A1，我希望它说活动".

I am trying to change the value of cell B1 depending on if cell A1's value is found in another spreadsheet. If there is a match, I want the cell to say "banned". If A1 isn't found in the other spreadsheet, I want it to say "active'.

我一直在玩这个游戏

= if(((VLOOKUP(A3，IMPORTRANGE(" https://docs.google.com/xyz ，"所有被禁帐户！$ G $ 2:$ G $ 300)，1，false))= A3，"被禁，"有效)

=if((VLOOKUP(A3,IMPORTRANGE("https://docs.google.com/xyz","ALL BANNED ACCOUNTS!$G$2:$G$300"),1,false))=A3,"Banned","Active")

，并且只能使其返回被禁".如果没有匹配项，则始终返回#N/A.

and can only get it to return "Banned". If there is no match, it always returns #N/A.

我该如何补救?

谢谢！

推荐答案

这是一个老问题，但是如果它对某人有所帮助，因为您在一栏中使用了VLOOKUP，因此唯一的选择就是它等于A3，或者找不到它，如果找不到，则VLOOKUP失败，而不是等于其他东西.因此，您可以使用以下公式:

This is an old question, but in case it helps someone, since you are using VLOOKUP in one column, the only choices are that it equals A3, or that it isn't found, and if it isn't found, VLOOKUP fails, rather than equaling something else. Therefore, you can use this formula:

=iferror(if(VLOOKUP(A3,IMPORTRANGE("https://docs.google.com/xyz","ALL BANNED 
ACCOUNTS!$G$2:$G$300"),1,false)=A3,"Banned","irrelevant"),"Active")

请注意，无关"是指可以更改为任意值，即0等.此结果永远不会发生，就像VLOOKUP不等于搜索单元格(在这种情况下为A3)一样，它将引发错误.因此，活动"因为如果有错误将显示结果，而不是如果VLOOKUP为false(因为不可能)则显示该值.

Note that "irrelevant" can be changed to anything, i.e. 0, etc. This result will never happen, as if VLOOKUP isn't equal to the search cell (in this case, A3), it will throw an error. So, "Active" as the result if there's an error will show rather than the value if VLOOKUP is false (since it can't be).

希望这对某人有帮助！

这篇关于使用VLOOKUP和IMPORTRANGE查找匹配项并返回两个潜在值之一的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！