How to extract all rows from a sheet that meet a criteria in a column?

Question

I have a sheet called Master, I want to extract all rows from that sheet that have the Value "Other Revenue" in Column E.

Been searching online for a way and cant seem to get it right

Answer 1

Here / screenshot(s) / comparison(s) refer:

(Excel versions per table below)

APPROACHES non-exhaustive sample

Method 1 - FILTER

=FILTER(C3:F7,--(C3:C7=I3))

Method 1 (Filter)

Office 365 - straightforward fn.

---

Method 2 - INDEX + SMALL/IF

=INDEX(C3:F7,SMALL(IF(C3:C7=I7,ROW(C3:C7)-ROW(C3)+1,""),SEQUENCE(SUM(1*(C3:C7=I7)),1,1,1)),SEQUENCE(1,COLUMNS(C3:F7),1,1))

Method 2 (INDEX + Ordered If)

Office 365, however only due to presence of SEQUENCE() which can be exchanged with other functions that are compatible with Excel versions. E.g. ROW(A1:INDIRECT("A"&SUM(1*(C3:C7=I3)),1)), (ROW(C3:C6)-ROW(C3)+1)

---

Method 3 - INDEX + SMALL/ROW

=INDEX(C3:F7,SMALL(IF(C3:C7=I11,ROW(C3:C7)-ROW(C3)+1,""),ROW(A1:INDIRECT("A"&SUM(1*(C3:C7=I11)),1))),COLUMN(C3:F3)-COLUMN(C3)+1)

M3 - Index + Small/Row alt. (non-365 vers.)

Any version of Excel

Variations naturally exist - e.g. INDEX(C3:F7,LARGE((ROW(C3:C7)-ROW(C2))*(C3:C7=I11),ROW(A1:INDIRECT("A"&SUM(1*(C3:C7=I11)),1))),COLUMN(C3:F3)-COLUMN(C2)) also does the trick.

---

Method 4 - IF (w, /w SORT)

=SORT(IF(C3:C7=I15,C3:F7,""),,-1)

Office 365 - due to SORT(), however, per M2 (comment)-M3, blank rows can be avoided through dynamic ranges defined by ROW() (as alternative to SEQUENCE(), SORT(), or FILTER() for that matter

M4 - Simple IF (with/without Sort)

---

COMPARISONS

Pros / Cons with each approach

---

References

Inspired, in part, by O. Cronquist (2021) - see here for related/various other approaches with user-friendly clear screenshots to aide.

---