TRIMRANGE with XLOOKUP
TRIMRANGE with XLOOKUP #Excel 365
=XLOOKUP(
TRIMRANGE(B4:B11),
Data[Names],Data[Age],
"Not Found")
TRIMRANGE is a powerful new function in Excel 365 that efficiently removes empty rows from the edges of a range. This is particularly useful when working with Dynamic Array Formulas or Optimizing Lambda Functions for Performance.
This new function and new references are currently available to Excel Insiders Beta Channel users running Version 2409 (Build 18020.2000) or later.