On Fri, 2 May 2008 23:49:01 -0700, prem wrote:

Hi guys this is my problem.

I have serial numbers in my entire A column and dates in my entire B column.

On a separate workskeet, I want to create a command that will check if the
serial number is a multiple of 8. If it is, I want it to return the date the
the corresponding B column/row. I came up with =IF(MOD(A2,8)=0,B2,"NA").

However, I do not want "NA" returned when the A column value is not a
multiple of 8. Instead I want it to check the value in the next A row, and if
that is not a multiple of 8, to keep going till it reaches a row where the
value the data in cell A is indeed a multiple of 8 and return the date from
the coresponding B column/row.

How might I achieve this? Thank you in advance.

Prem

If your serial numbers are randomly entered (or entered with occasional skips
in the sequence), then this array formula, entered into some cell and filled
down until it produces an error, should return the adjacent dates.

SerialNums and Dts are named ranges, but you can substitute any single column
range reference or NAME.

To enter an array formula, hold down <ctrl<shift while hitting <enter. Excel
will place braces {...} around the formula:

=INDEX(Dts,SMALL((MOD(SerialNums,8)=0)*ROW(SerialN ums),
ROWS($1:1)+SUMPRODUCT(--(MOD(SerialNums,8)<0))))

--ron