"lsy" wrote
yes.. this is exact what i want!! thanks...

Glad to hear that ..

can u please explain to me what

<=IF(ISERROR(SMALL(Sheet1!$F:$F,ROWS($A$1:A1))),"" ,INDEX(Sheet1!C:C,MATCH(SM
ALL(Sheet1!$F:$F,ROWS($A$1:A1)),Sheet1!$F:$F,0)))
means??? so that i can reuse it!!

The construct functions along these lines ..

In Sheet1:

Col F is a helper col where we frame up the "filter" criteria to pick out
values in the key col C which will match with what is input in Sheet2's A1.

Matched values in col C will return arbitrary row numbers in col F. These
arb row numbers in col F will in turn be read by the extract formulas in
Sheet2

TRIM is used to improve robustness in the matching. It'll remove extraneous
space(s) present - if any- which might otherwise throw valid matching cases
off.

In Sheet2:

The core formula placed in X2 is the
INDEX(Sheet1!C:C,MATCH(SMALL(...),...)).

SMALL(...) points to the criteria col F in Sheet1, and returns an ascending
sort of the arbitrary row numbers in col F, with any blanks thrown below.

MATCH(SMALL(...),...)) then returns the positions of the values returned by
SMALL(...) matched against the lookup_array, which again is Sheet1's col F.

INDEX(Sheet1!C:C, ..) then simply retrieves the corresponding values from
Sheet1's col C. INDEX(Sheet1!C:C, ..) in X2 copied across to Y2 will result
in INDEX(Sheet1!D:D, ..), i.e. increment the col C to D, hence returning
corresponding values from col D in Sheet1 into col Y.

Remember to fix the references to Sheet1's col F in the formula, i.e. use
Sheet1!$F:$F (with the dollar signs).

The error trap " IF(ISERROR(SMALL(...),"",INDEX(...)) " is used to return
blanks: "" (instead of #NUM!s) for a cleaner "blank" look once the returns
for all the arb row numbers from Sheet1's col F have been exhausted in
Sheet2

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----