Filter: Use Filter to find a set of records that match one or more criteria and to discard those that don’t.
– Filter function return a table records as per the condition or criteria that is being applied.
– If no records found then filter function returns empty table.
– Syntax: Filter (Table, Condition1 [, Condition2, …])
Search: Use Search to finds records in a table that contain a string in one of their columns.
– Search function return a table records as per the search string that is being applied.
– If no records found then Search function returns empty table.
– Syntax: Search (Table, SearchString, Column1 [, Column2, …])
Lookup: LookUp returns only the first record found, after applying a formula to reduce the record to a single value.
– If no records found then filter function returns a blank.
– Syntax: LookUp (Table, Formula [, ReductionFormula])
Let’s take an example to better understand Filter, Search and Lookup Consider a table “Employee”
1. Filter (Employee, “Employee ID” = “As0002”): Returns records where Employee ID equals to As0002.
Below table shows the return records
2. Search (Employee, “”, “Employee ID“): Because the search term is empty, all records are returned.
Below table shows the return records
3. Search (Employee, “An”, “Name“): Returns records where the string “An” appears in the Name, independent of uppercase or lowercase letters.
Below table shows the return records
4. LookUp (Employee, “Name” = “Annu” ): Searches for a record with
Name equal to “Annu”, of which there is one record. Since no reduction formula was supplied, the entire record is returned.
Return records: { Employee ID: An0001, Name : “Annu”, Phone Number:
2345659089 }
5. LookUp (Employee, “Name” = “Annu”, ” Employee ID“): Searches for a record with Name equal to “Annu”, of which there is one record. For the first record that’s found, returns the Employee ID of that record
Return records: { Employee ID: An0001}
6. LookUp (Employee, “Name” = “Divya”, ” Employee ID“): Searches for a record with Name equal to “Divya”, of which there are none. Because none were found, Lookup returns blank.
Return records: blank