Extract All Data from a Range

[msharma864512]

Hi,

I want to extract all the values from a range matching the data from a certain cell. Details are mentioned in the attached excel.

 

[GraH - Guido]

alternative to any formula is simply using a pivot and slicer.

 

[msharma864512]

alternative to any formula is simply using a pivot and slicer.

Thanks G,

But I am looking for formula only.

I dont want to do it through slicer or pivot

 

[bosco_yip]

Formula solution,

In "Sheet 3" B3 formula copied down :

=IFERROR(INDEX(Sheet1!B:B,AGGREGATE(15,6,ROW(Sheet1!A$2:A$99)/(Sheet1!A$2:A$99=LOOKUP("zzz",A$3:A3)),ROW(A1)-MATCH("zz",A$3:A3)+1)),"")




Regards
Bosco

 

[msharma864512]

Formula solution,

In "Sheet 3" B3 formula copied down :

=IFERROR(INDEX(Sheet1!B:B,AGGREGATE(15,6,ROW(Sheet1!A$2:A$99)/(Sheet1!A$2:A$99=LOOKUP("zzz",A$3:A3)),ROW(A1)-MATCH("zz",A$3:A3)+1)),"")


View attachment 65832

Regards
Bosco

Thank you Bosco, this is really helpful

 

[msharma864512]

Formula solution,

In "Sheet 3" B3 formula copied down :

=IFERROR(INDEX(Sheet1!B:B,AGGREGATE(15,6,ROW(Sheet1!A$2:A$99)/(Sheet1!A$2:A$99=LOOKUP("zzz",A$3:A3)),ROW(A1)-MATCH("zz",A$3:A3)+1)),"")


View attachment 65832

Regards
Bosco

Hi Bosco,

I was able to use the formula u shared to extract all the value from the range, now I am trying to add one more condition to pull the values from the range.
Details are in the attached file.

Regards,
Manish