Excel is one of the most common Data Source for Power Apps, that can have more than 2000 rows. End Users are continuously updating existing rows or adding new rows into it, which again increases its size.
Please note that my Excel file is stored in OneDrive for Business and I am using Excel Online (Business) connector.
We already know that Canvas Power Apps has a limitation to access only 2000 at one time. Hence, if we want to access 2001th row, we need to be careful while selecting the formula to get that row because of Delegation warning.
If we use Filter function, we can only get up to 2000th row. To resolve this issue, we can use Search function, even for the dropdowns.
We can write below code on OnChange event of the Text Input or DropDown or ComboBox.
ClearCollect(empInfo,
If(!IsBlank(txtEmpID), Search('EmpInfo',txtEmpID.Text, "EmpID"),
If(!IsBlank(txtName), Search('EmpInfo', txtName.Text, "EmpName"),
If(!IsBlank(drpRole), Search('EmpInfo', drpRole.Selected.Value, "Role"),
If(!IsBlank(cmbCity), Search('EmpInfo', cmbCity.Selected.Value, "City")
))))
)