How would I extract row information based on a value on a column in Excel or Google Sheets?

Question

I'm building an excel spreadsheet for a project I'm working on and I'm running into an issue relaying the data.

If I have data like so:
Name Info1 Info2 Condition1 Condition2
foo1      3        9        X
foo2      8       10                               X
foo3      2        7        X                      X

I have an formula that, based on certain conditions, generates a "condition" flag. I then want to develop a way on another sheet, pull from this data set all the lines that contain the "Condition".

Example: Cell generates Condition1, display all the rows and values that have an "X" in the column "Condition1".

I know I can use filters but this isn't going to be custom-facing data. If filters are the only way, is there a script that can be done to auto-filter based on the value of a cell so the user doesn't see the other data (unless they manually edit the filter)?

I've tried VLOOKUP and HLOOKUP combinations as well as INDEX and dabbled in MATCH. The issue I'm having is if I have 40 items that match Condition1 and then the next day it auto-switches to Condition2 and it has 10 items, I'm finding difficulty in writing a script to generate all of those items.

Answer 1

If you are using Google Sheets or Excel 365 then you can use FILTER(). If your version of Excel supports the AGGREGATE() function, then it can be used. If you are using a Jurassic version of Excel, then you can use MATCH() to retrieve the data you require. Say we have:

and we want records where cond1 has an x. Namely rows 2, 4, 7, 11, 13, 17, and 19.

In G1 enter:

=MATCH("x",D:D,0)

In G2 enter:

=IFERROR(MATCH("x",INDEX(D:D,G1+1):INDEX(D:D,9999),0)+G1,"")

and copy downward. Each MATCH() formula examines a separate chunk of column D:

The MATCH() formula in G1 starts at the top of column D, the MATCH() formula in G2 starts one cell below the match found in G1, etc. Once we have the row numbers, INDEX() can be used to retrieve any info on that row.