https://store.brics-practice-management.com

Searching Data Tables and returning filtered subsets of data is key in all database management systems. 

For sets of data with 100's or 1000's of records simple searches for one field or a combination of fields with an AND connector is easy. 

= filter ({   }, condition1, condition 2)  

​=filter({Contacts!$A:$A, Contacts!$B:$B,Contacts!$C:$C},Contacts!$D:$D = B8) -->  wher B8 is a select box for D data...

=if(B8="","", ifna(filter({Contacts!$A:$A, Contacts!$B:$B,Contacts!$C:$C},Contacts!$D:$D = B8),"No records")) -->  with data validation

Doing searches for 10,000's or more and OR conditions is NOT easy.  The select box/drop downs start to seize up and the ORs require custom programming. There is a "query" function that uses SQL   HOWEVER, it requires reference to columns by letter and if the table columns move it breaks. Those references are absolute not reltaive as the sql strings are static text. The column refs in traditional spreadsheets are relative refs not absolute.  Thus, the "query" fxn is NOT a viable solution for most use cases unless the table format will never change. and that is not ideal for customizable software. Luckily with a little help from youtube and the use of "composite index columns we create we can do many things. 

Use Filter with Search Function for  wildcard match 

https://www.youtube.com/watch?v=s4bjzX_NQC4 

For one criteria per field on only... 

= filter (range, search( value, sub-range), search(value,sub-range) )

For two criteria per single field may be able to use OR with filter OR use regex...

= filter (range, regExmatch(subrange, "value1 | value2")

OR if you can make a composite key where multiple fields are concatenated in a single field for search,  you can do this...

= filter(range, search( value, sub-range that is concatenated values )  

=if(B8="","--", ifna(filter({Contacts!$A:$A, Contacts!$B:$B,Contacts!$C:$C},Search(B8,Contacts!$X:$X)),"No records"))

and if using the red row techniques with padded row, it looks like this...

=

{ 

ARRAY_CONSTRAIN(arrBlanks,1,3);

if(B7="",{"--","",""},ifna(filter({Contacts!$A:$A, Contacts!$B:$B,Contacts!$C:$C},Search(B7,Contacts!$X:$X)),{"No Match...","",""}))

}

=sort ( filter ( SO_headers_tbl!AB13:AB , SO_headers_tbl!T13:T = "ar_open" ), 1 , true )

Array formual for multipl ifs only works iwth IF, not with IFS or SWITCH, this is cleanest syntax. .

=Arrayformula(
If(J10:J="","",
if(J10:J="USPS", hyperlink("https://tools.usps.com/go/TrackConfirmAction.action?tLabels=" & K10:K, "View on USPS"),
if(J10:J="UPS", hyperlink("https://ups.com=" & K10:K, "View on UPS"),
))))

NOTE: the trailing comma on the last line is proper and fine, it suggest there is no follownig else and sets you up to add more lines without forgetting teh comma. 

Multiple Filters stacked for Payment and Shipment Details

={

"","";

"Pmt ID","Details"; 

filter({Payment_Journal!T4:T,Payment_Journal!U4:U},Payment_Journal!D4:D=D4); 

"ShipID","Details";

filter({Shipping_Journal!V10:V,Shipping_Journal!W10:W},Shipping_Journal!D10:D=D4)

}

Bill to vs Ship to as priority keys...

https://www.reddit.com/r/salesforce/comments/m8ot8d/data_integration_accounts_by_bill_to_or_ship_t0/

See