i want the formula to get the values of one column of large data against the refencere cell

[giri ravirrala]

i want the formula to get the values of one column of large data based on the refencere cell, same like vlookup formula but the value are more than one noumber in the data , i am attaching the file
the data having the column like material no, lot no, ID no, date
Material Lot No ID No Date
200000303 50839699 ACHH004547 12/7/2014
200000303 50840785 ACHH004600 12/9/2014
200000303 50840963 ACHH004628 12/11/2014
200000305 50839658 ACHH004546 12/10/2014
200000305 50840777 ACHH004599 12/15/2014
200000305 50840935 ACHH004620 12/16/2014
200000321 50839177 ACHH004507 12/3/2014
200000321 50839509 ACHH004530 12/4/2014
200000321 50839510 ACHH004531 12/5/2014
200000322 50838850 ACHH004487 12/9/2014
200000322 50839131 ACHH004498 12/5/2014
200000322 50839224 ACHH004516 12/9/2014
200000322 50839225 ACHH004517 12/14/2014
200000322 50839512 ACHH004533 12/9/2014
200000322 50839517 ACHH004534 12/10/2014
200000322 50839704 ACHH004552 12/10/2014
200000322 50839705 ACHH004553 12/12/2014
if I am trying to make table of Lot No & ID No as against a reference value
(for example for the material 200000303)
Material Lot No ID No
200000303 50839699 ACHH004547
200000303 50840785 ACHH004600
200000303 50840963 ACHH004628

it will help me to segregate the huge data based on material No and presently i am using the Data/filter to get the values and using for onather calulations based on reference of lot No
, i am requesting to help me , for the formula based on the refernce material i want lot No and ID no.
thanks & regards,
Giri.

 

[Asheesh]

Welcome to the forum...

In the attached file...put the following array formulae

In H3, IFERROR(INDEX($C$3:$C$19,MATCH(0,COUNTIF($H$2:H2,$C$3:$C$19)+($B$3:$B$19<>$G$3),0)),"") then drag it down as per your requirement

In I3, IFERROR(INDEX($D$3:$D$19,MATCH(1,($B$3:$B$19=$G$3)*($C$3:$C$19=$H3),0)),"") then drag it down as per your requirement...

Both are to be array entered...

I am unable to attach the file...let me know if you face any issues...

 

[Asheesh]

Attached...See the cells highlighted in yellow...

 

[giri ravirrala]

thank you very much, its helped me alot