Automated system for data search and matching in Google Sheets

Complex formulas based on LET, MAP, ARRAYFORMULA, REGEXREPLACE, XLOOKUP for automatic searching and matching of records between multiple tables without using scripts.

Key functions of the solution:
Automatic processing and normalization of text data:
conversion to lowercase;
removal of extra spaces;
cleansing of special characters and non-breaking spaces.
Mass data processing through ARRAYFORMULA.
Finding matches between several sheets under complex conditions.
Using logical filters to select only the necessary records.
Building a backup search mechanism:
primary search in the main database;
automatic switch to an alternative data source in case of no results.
Processing large data arrays without user manual intervention.
Automatic return of up-to-date information in real-time.
Technologies used:
Google Sheets
LET
MAP
LAMBDA
ARRAYFORMULA
REGEXREPLACE
XLOOKUP
Logical arrays
Text data processing
Business result:
The created solution allowed for the complete automation of the process of searching and linking data between multiple tables, reducing the amount of manual work for operators and minimizing errors in information processing.