The Excel Dynamic Array XLOOKUP function is a new and flexible replacement for the older legacy function VLOOKUP and the HLOOKUP and LOOKUP function.  XLOOKUP supports exact and approximate matching, wildcards (* ?) for partial matches, and lookups in vertical or horizontal ranges. The lookup ranges can be located anywhere, not constrained to a contiguous range as with the legacy VLOOKUP which means we are not limited to right lookups.

XLookup.xlsx