Home

Search until true if contains specific text, return only true in multiple columns

mb-landsearch
New Contributor

Hello and thank you in advance for any assistance, this forum has helped numerous times over the years.
I have uploaded a file with 2 sheets: "Project Lease Summary" is the desired result, searching the data on "Project Lease Details".
Specifically I am searching column A on "Project Lease Details", for cells containing "Totals", and returning only true results on "Project Lease Summary", in column A: the number before the text "Totals" and then related data from the "Totals" row into adjacent cells.
I have been trying variations of =IFERROR(INDEX(LeaseStatusDetails[Tract No.],SMALL(IF(LeaseStatusDetails[Tract No.]="*Totals*",ROW(LeaseStatusDetails[Tract No.])-ROW(LeaseStatusDetails[@[Tract No.]])+1),ROWS(A$3:A3))),"")
using ctrl+shift for an array formula but am not getting it.

2 Replies

Beside formula there is filter and copy paste, advanced filter or Power Query.

 

Thank you, I was making it too complicated. The advanced filter works great.