vlookup

[joe]

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

example:
A B C(Fom 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

 

[Biff]

Hi!

For those values in column A but not in column B:

Enter this formulas as an array - CTRL,SHIFT,ENTER
and copy down until you get #NUM! errors:

=INDEX($A$1:$A$5,SMALL(IF(COUNTIF($B$1:$B$5,$A$1:$A$5)
=0,ROW($A$1:$A$5)),ROW(1:1)))

For those values in column B but not in column A:

Enter this formulas as an array - CTRL,SHIFT,ENTER
and copy down until you get #NUM! errors:

=INDEX($B$1:$B$5,SMALL(IF(COUNTIF($A$1:$A$5,$B$1:$B$5)
=0,ROW($A$1:$A$5)),ROW(1:1)))

Biff

 

[Joe]

Hi,

seem to me it is not right , it returns the smallest
value.

regards,
Joe

 

[Frank Kabel]

Hi
see your other post

 

[Biff]

Hi!

It does exactly what you wanted. You may have to adjust
the range references to suit your needs.

Biff