Help VLOOKUP function

[Ali Noor]

Dear All,

I am in process of book reconciliations and for that i need to know that how
can we use Lookup function to compare value in Asending arranged two Coulmns.

My purpose is,

1. Match the values in 2 side by side coulmns A n B ( suppose)
2. Then arrange the Matched values in these coulmns so that
same values come face to face and those which are not identical goes in
the last
of the relevant column

So that i can not only compare the data in tow coulmns but aslo get it
arranged


Reg,
Ali.

 

[paul.robinson]

Hi
A work around:

1. Put the first column of data in column "A" and the second in column
"D"
2. Name the column "A" of numbers "Data1" by selecting it and doing
Insert, Name, Define and typing Data1 in the name box.
3. Do the same for the second column "D" of data - call it Data2
4. In the first cell of column "B" next to Data1 put the formula
=Vlookup(Data1, Data2, 1, False)
and fill this formula down.
Wherever you see a #N/A, the number is in Data1 but not in Data2.
5. In the first cell of column "E" next to Data2 put the formula
=Vlookup(Data2, Data1, 1, False)
and fill this formula down.
Wherever you see a #N/A, the number is in Data2 but not in Data1.
6. sort the data in Columns "A" and "B" ascending for column "B". This
will put all the #N\A cells to the bottom.
7. sort the data in Columns "D" and "E" ascending for column "E". This
will put all the #N\A cells to the bottom.

8. Remove the #N/A characters from the bottom of list in column B.
Replace them with the numbers in column D that have a #N\A next to
them.

regards
Paul

 

[Ali Noor]

Dear PAUL,

i have tried ur under mentioned method how ever there seems to be some
problem,

actual data and problem is as below,

Data1 Data2
4471 4471
4472 4472
4473 4475
4474 4476

as per ur instruction i have inserted data names and all how ever #N/A is
being shown in all the cells where as it should have come for 4473 and
4474.... with ref to Data 1 only..... there is some problem..... there is too
much data i have to compare...... and i will be thankful..... actualy there
is complete range in two coulmns.... and i have to compare it...

Do help me i have even sent Excel sheet on ur email address