比较两个列值 [英] Compare two column values

问题描述

Hello Friends，



我需要比较excel列中的两个多值，比较结果是第三列。请在下面找到我的要求示例。



提前感谢。

< tr>

第1列	第2列
Scott;埃格利斯; Endacott;菲多;绿色;希巴德; Olney的	斯科特; Olnei; Endacot;菲多;绿色;白色;希巴德; Eglise

比较结果如下：

奥尔尼; Endacott;白色



请任何人帮助我。

Hello Friends,

i need compare two multiple values from excel column and compare result comes third column. please find below my requirements example.

thanks in advance.

Column 1	Column 2
Scott; Eglise; Endacott; Fido; Green; Hibbard; Olney	Scott; Olnei; Endacot; Fido; Green; White; Hibbard; Eglise

Compare result as follows:
Olney; Endacott; White

kindly any one help me.

推荐答案

制作东西更容易在第1列中给出数据名称例如列表。



计算的第一部分然后需要在该列表上执行 VLOOKUP（），寻找确切的比赛。



如果找不到数据，VLOOKUP将返回#N / A，因此您还需要一个名为的函数ISNA（）如果一个单元格包含，或者函数返回，则返回TRUE＃N / A



因为你实际上想列出那些不匹配的你还需要函数 NOT（）来反转查找的效果。



最后把所有这个函数在你的公式中一起返回一个 IF（）函数，在真假部分返回一个空白，在假部分中返回第2列中的项目





我在A列中有第1列值，在B列中有第2列值。我已经使用 Insert，Name，Define 来调用B列中的所有数据 List 。然后我对A列和B列进行了排序，以便更容易查看结果



计算的第一部分是

To make things easier give the data in column 1 a Name e.g. "List".

First part of your calculation then needs to do a VLOOKUP() on that list, looking for an exact match.

VLOOKUP returns #N/A if it can't find the data so you will also need a function called ISNA() which returns TRUE if a cell contains, or a function returns, #N/A

Because you actually want to list the ones that don't match you'll also need the function NOT() to reverse the effects of the lookup.

Finally put all of this together into an IF() function in your formula returning a blank for the true part and the item from column 2 in the false part


I have your column 1 values in "Column A" and column 2 values in "Column B". I've used Insert, Name, Define to call all the data in Column B List. I then sorted column A and column B just to make it easier to view the results

First part of calculation is

=VLOOKUP(A1, List, 1, FALSE)

返回结果

which returns results

Eglise	Eglise	Eglise
EndacottEndacot	#N/A
Fido	Fido	Fido
Green	Green	Green
Hibbard	Hibbard	Hibbard
Olney	Olnei	#N/A
Scott	Scott	Scott
	White	#N/A

如果我将它包装在ISNA（）函数中然后在NOT（）函数中...

If I wrap that in the ISNA() function and then in the NOT() function ...

=NOT(ISNA(VLOOKUP(A1, List, 1, FALSE)))

然后我得到结果

then I get results

Eglise	Eglise	TRUE
EndacottEndacot	FALSE
Fido	Fido	TRUE
Green	Green	TRUE
Hibbard	Hibbard	TRUE
Olney	Olnei	FALSE
Scott	Scott	TRUE
	White	FALSE

最后，将其放入IF（）语句中以整理输出...

Finally, put that into an IF() statement to tidy up the output ...

=IF(NOT(ISNA(VLOOKUP(A1, List, 1, FALSE))), "", B1)

哪个给出结果

Which gives results

Eglise	Eglise	
EndacottEndacot	Endacot
Fido	Fido	
Green	Green	
Hibbard	Hibbard	
Olney	Olnei	Olnei
Scott	Scott	
	White	White

这与您所说的预期结果非常接近。我怀疑你有一个打字错误，因为你似乎试图合并两列的结果。然而，这个有效的例子应该足以让你走上正确的道路

Which is very close to what you said your expected results were. I suspect you have a typing error in there as you appear to be trying to merge results from two columns. However this worked example should be enough to set you down the right path

这篇关于比较两个列值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！