Excel 反转 VLOOKUP
我有两个列表,一个从 1 到 10,另一个从 1 到 5。我使用 VLOOKUP 在大列表和小列表中搜索,它返回从 1 到 5 的值,从 6 到 10 返回 N/A。 表达式如下: =VLOOKUP(A:A;B:B;1;FALSE)
我真的需要它返回从 6 到 10 的值(不在第二个中的值)列表,但位于第一个)。我该怎么做?不必位于同一列或使用 VLOOKUP。我想要一个解决方案来返回不是小列表的值。
谢谢你!
I have two lists, one from 1 to 10 the other from 1 to 5. I use the VLOOKUP to search in the large list the small list and it returns the values from 1 to 5 and from 6 to 10 it returns N/A.
The expession is like this : =VLOOKUP(A:A;B:B;1;FALSE)
I would really need it to return the values from 6 to 10 (the values that are not in the second list, but are in the first one). How can i do this? Doesn;t have to be on the same column or to use VLOOKUP. I would like a solution to return the values that are NOT the the small list.
Thank you!
首先,我将描述我的测试数据:
A 列包含 A1 - A10 中的 1,10
B 柱包含 B1 - B5 中的 1,5
C 列包含公式
VLOOKUP(A1,B1:B10,1,FALSE)
,其中第一个参数已相应更新。使用该公式,我在 C1 - C5 中得到 1,5,在 C6-C10 中得到 N/A
如果我将公式更改为
=IF(ISERROR(VLOOKUP(A1,B1:B10,1,FALSE)),A1, "")
(再次适当更新 A1),我在 C1-C5 中得到“”,在 C6 - C10 中得到 6-10。这是您要找的吗?
First I'll describe my test data:
Column A contains 1,10 in A1 - A10
Column B conatins 1,5 in B1 - B5
Column C contains the formula
VLOOKUP(A1,B1:B10,1,FALSE)
with the first argumnent updated as appropriately.Using that formula I get 1,5 in C1 - C5 and N/A in C6-C10
If I change the formula to
=IF(ISERROR(VLOOKUP(A1,B1:B10,1,FALSE)),A1,"")
(again with the A1s updated appropiately), I get "" in C1-C5 and 6-10 in C6 - C10.Is this what you were looking for?