• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Extract All Data from a Range

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.
 

Attachments

  • Pull alll the values using matching the criteria.xlsx
    12.2 KB · Views: 8
alternative to any formula is simply using a pivot and slicer.
 

Attachments

  • Copy of Pull alll the values using matching the criteria.xlsx
    20.1 KB · Views: 4
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)),"")


65832

Regards
Bosco
 

Attachments

  • Pull alll the values using matching the criteria.xlsx
    13.8 KB · Views: 13
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
 

Attachments

  • Sample.xlsx
    51.2 KB · Views: 5
Back
Top