Dear Experts,

Please help for the following condition:

1. I have one sheet "A" which contain of database, the field structure as

follows:

Field A = Date

Field B = SN

Filed C ...

I have a lot of records, which can contains the same SN (Serial Number) but

with different Removal Date.

2. on the other worksheet...i create the following field

COL A = SN

COL B = First Removal Date

COL C = Second Removal Date.

COL D = Third Removal Date.

Please guide to which function i should use? As i use VLOOKUP, it will be go

the first occurance of the date removal...(the result works properly in FIELD

B), but not on Column C, D, etc.

thank you for your kind help and TIA.

respectfully,

andri

Here's one method that might work:

I assume you enter your serial numbers manually starting in A2

SN is the named range of serial numbers on Sheet"A" (e.g. perhaps A1:A10000).

Unless you are using Excel 2007+, do NOT define SN as a full column.

This formula must be **array-entered**:

B2: =LARGE(IF(SN=$A2,Dates,0),COLUMNS($A:A))

----------------------------------------

To **array-enter** a formula, after entering

the formula into the cell or formula bar, hold down

<ctrl><shift> while hitting <enter>. If you did this

correctly, Excel will place braces {...} around the formula.

Then Fill Right and down as required.

IMPORTANT NOTES:

The formula will return a zero if there are more possible Removal Dates than

entries for a particular serial number. You could TEST the return to see if it

returns a zero, or you could use a custom format: m/d/yyyy;; so the zero's

will not be displayed.

-------------------------------------------------------

As written, this will return the values in descending order (latest Removal

Date first). If you want the answers sorted the other way, then:

=SMALL(IF(SN=$A2,Dates,1E+307),COLUMNS($A:A))

and use this custom format: [<2958466]m/d/yyyy;;

--ron

.