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

You can use this array** formula:

=SMALL(IF(A!$C$2:$C$100=$A2,A!$A$2:$A$100),COLUMN( A1))

Copy across and down as needed. Note that if a nth instance of a SN does not
occur, the formula will return the #NUM! error for that instance.

**Array formulas must be confirmed using Ctrl+Shift+Enter
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"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

Dear Luke,

i cannot implement that formula, here is what i am targetting

Database in sheet "A"
Serial Number Date
X1 1-Jan-07
X2 1-Feb-07
X3 1-Mar-07
X1 1-Apr-07
X1 1-May-07
X4 1-Jun-07
X2 1-Jul-07
X3 1-Aug-07
X1 1-Sep-07
X3 1-Oct-07
X2 1-Nov-07


In sheet B.
I would like to match for SN, then search the first removal, then second
removal if any...so on.
1st Rmvl 2nd Rmvl 3rd Rmvl 4th Rmvl
X1 1-Jan-07 1-Apr-07 1-May-07 1-Sep-07
X2 1-Feb-07 1-Jul-07 1-Nov-07
X3 1-Mar-07 1-Aug-07 1-Oct-07
X4 1-Jun-07


"Luke M" wrote:

You can use this array** formula:

=SMALL(IF(A!$C$2:$C$100=$A2,A!$A$2:$A$100),COLUMN( A1))

Copy across and down as needed. Note that if a nth instance of a SN does not
occur, the formula will return the #NUM! error for that instance.

**Array formulas must be confirmed using Ctrl+Shift+Enter
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"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

Dear Luke,

Excellent formula, case solved...

thank you and highly appreciated.

respectfully,
andri

P/S : Please ignore the previous message :(

"Luke M" wrote:

You can use this array** formula:

=SMALL(IF(A!$C$2:$C$100=$A2,A!$A$2:$A$100),COLUMN( A1))

Copy across and down as needed. Note that if a nth instance of a SN does not
occur, the formula will return the #NUM! error for that instance.

**Array formulas must be confirmed using Ctrl+Shift+Enter
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"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