"TUNGANA KURMA RAJU" wrote:
I am having a range a2:a100 student's Names,
b2:b100-standard(std1,std2 etc...), c2:c100-Marks.
Basing on D1 value(say D1=std2), I want to extract std2 values of range
a2:c100 to D2:F100( all 3 columns data).
Blank range should shown as Blank("").
I don't want to it using filter.

One way using non-array formulas (& simple entire col references) ..

Create a DV in D1 to select the standard, viz:
Data Validation, Allow: List, Source: Std1,Std2,Std3,etc
Paste the col headers in A1:C1 into E1:G1

Then place in D2:
=IF(B2="","",IF(B2=$D$1,ROW(),""))

Place in E2:
=IF(ROW(A1)COUNT($D:$D),"",INDEX(A:A,MATCH(SMALL( $D:$D,ROW(A1)),$D:$D,0)))
Copy E2 to G2

Just select D2:G2 and copy down as far as required to cover the max expected
extent of source data. The required results depending on the DV selection
made in D1 will display in cols E to G, all neatly bunched at the top. Mask
col D by formatting the font in white - except for the DV cell in D1.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---