Excel 2002: Vlookup formula not working

[Mr. Low]

Dear Sir,

Lets consider the following worksheet:

A B C D E
Original Extract Sequence
1 0424905001 424905 Y 424905
2 0424907001 424907 Y 424906
3 0424908001 424908 Y 424907
4 0424909001 424909 Y 424908
5 0424910001 424910 Y 424909
6 0424911001 424911 Y 424910
7 0424912001 424912 Y 424911
8 0424913001 424913 Y 424912
9 0424914001 424914 Y 424913


I used =MID(A1,2,6) to extract for the six digit reference number in column
B.

I put in “Y†in column C and a series of running numbers in column D.

To check if the document reference are in running sequence, I use the
formula =VLOOKUP(D1,B1:C9,2,FALSE) in cell E1 and copy down.

My intention was for cells in Column E to return with “Y†when the
document numbers are in running order and #NA for missing numbers.

However I noticed that this formula does not work, with all the cells in
column E returns with #NA.

I tried to used =TRIM(CLEAN( ) for column B data and it does not solve the
problem. Neither did it work by converting column B to numbers or text.

Is there any other ways or special formula to overcome this problem?



Thanks

Low

 

[Mike H]

If you simply looking for gaps then try this in E2 and drag down:-

=IF(D1=D2-1,"Y","N")

It will display N or anything you want when it encounters a gap or out of
sequence number.

Mike

 

[Mr. Low]

Helo Mike,

I would like to modify your formula to =IF(B1=B2-1,"Y","N") as I need to
check on columnb B. Yes, indeed this formula could tell that there are gaps
between numbers in column B but it could not list down the missing numbers.

If I compare the numbers in column B anmd D using the VLOOKUP formula, I
would be able to list down all the missing numbers when the output at column
E is #N/A.

My problem is what look like same number in column B and D are actually not
identical. If I used IF (B1=D1,"Y","N"), it always give a no answer.

May I kinow how to convert these two columns to become numbers or text so
that VLOOKUP or other Excel formulas could work ?

Thanks

Low

 

[Mr. Low]

Hello Mike,

I missed out one point. Column A data is obtained from a report generated by
another business system in Excel format.

Thanks

Low