vlookup

[Joe]

Hi,
how to use vlookup that actually select those values in
column A which does not appear in column B and vice versa.

example:
A B C(From column A) D(From column B)
2.3 1.3 1.2 0.4
1.3 0.4 1.4 2.6
3.6 2.6
1.2 2.3
1.4 3.6


many thanks

regards,
Joe

 

[Frank Kabel]

Hi
enter the following array formula in C1 (entered with
CTRL+SHIFT+ENTER):
=INDEX($A$1:$A$10,MATCH(0,COUNTIF($B$1:$B$10,$A$1:$A$10),0))

In C2 enter the array formula:
=INDEX($A$1:$A$10,MATCH(1,(COUNTIF($B$1:$B$10,$A$1:$A$10)=0)*(COUNTIF($
C$1:C1,$A$1:$A$10)=0),0))

and copy this down until you got an #NA error. If you want to prevent
this as well use the following array formula in C2 instead:
=IF(ISNA(MATCH(1,(COUNTIF($B$1:$B$10,$A$1:$A$10)=0)*(COUNTIF($C$1:C1,$A
$1:$A$10)=0),0)),"",INDEX($A$1:$A$10,MATCH(1,(COUNTIF($B$1:$B$10,$A$1:$
A$10)=0)*(COUNTIF($C$1:C1,$A$1:$A$10)=0),0)))