From: Andri on 8 Feb 2010 10:48 Dear Ron, same to you, excellent formula, case solved by now. thank you for your kind guidance...highly appreciated. respectfully, andri "Ron Rosenfeld" wrote: > On Mon, 8 Feb 2010 04:12:01 -0800, Andri > wrote: > > >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 > while hitting . 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 > . >