G

#### Guest

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