vlookup
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,(COUN TIF($B$1:$B$10,$A$1:$
A$10)=0)*(COUNTIF($C$1:C1,$A$1:$A$10)=0),0)))
--
Regards
Frank Kabel
Frankfurt, Germany
"Joe" schrieb im Newsbeitrag
...
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
|