How to selectively filter the searches based on a criteria (Index function)

**mutzie** · 01-24-2019, 08:46 PM

Hiya!

On cells H11:K33, a list of data gets showed and filtered when any of the Active X buttons (Acct 1, 2 and 3 where the person is designated) are selected which shows the Employee Name, Hire Date, Tenure Age and Status. How can the formula in these cells adjusted to only show those people with statuses of OVERDUE, RED FLAG, NEARING, ONGOING and FALSE. Basically, if the employee has a status of REGULAR, do not include his/her data on the list that gets filtered?

Thank you so much respectively!

**Glenn Kennedy** · 01-24-2019, 09:13 PM

Formulae like this:

=IFERROR(INDEX('Employee Masterlist'!C:C,AGGREGATE(15,6,ROW('Employee Masterlist'!$A$2:$A$300)/(('Employee Masterlist'!$I$2:$I$300=$F$2)*('Employee Masterlist'!$U$2:$U$300<>"Regular")),ROWS(A$19:A19))),"")

will do that.

Any reason for using the outdated .xls format in your sample???

**mutzie** · 01-24-2019, 09:34 PM

Hello Glenn Kennedy,

Accidentally saved it on an older version but is really using an .xlsx for these. Thank you so much for the help! I really will not be able to think about this formula, just an additional learning question

<>"Regular" - this crosschecks the term in !$U$2:$U$300 of the Masterlist and automatically omits them right?

**Glenn Kennedy** · 01-24-2019, 09:39 PM

Yep. It returns TRUE for anything that is NOT regular.

You're welcome and thanks for the rep.

How to selectively filter the searches based on a criteria (Index function)

LinkBack

Thread Tools

Rate This Thread

Display

How to selectively filter the searches based on a criteria (Index function)

Re: How to selectively filter the searches based on a criteria (Index function)

Re: How to selectively filter the searches based on a criteria (Index function)

Re: How to selectively filter the searches based on a criteria (Index function)

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Filter and extract list of data based on criteria using INDEX MATCH

[SOLVED] INDEX Function to return a value based on multiple criteria.

[SOLVED] 2 or more Index Match Searches in one?

[SOLVED] Selectively Import Rows into Excel from a CSV file based on criteria

Index/Match Formulas - Search/Filter Based on Specific Criteria

How to selectively sum multiple criteria using only one range?

[SOLVED] Macro function needed for varied searches using a data filter

Bookmarks

Bookmarks

Posting Permissions