Searching Left and From the Bottom
Look up in any direction including right-to-left and last-to-first.
Direction Freedom
One of VLOOKUP's biggest limits was that it could only return values to the right of the lookup column. XLOOKUP has no such rule.
Because you supply the lookup array and return array separately, the answer can be anywhere - left, right, above, or below the column you search.
=XLOOKUP(D2, B2:B20, A2:A20)Looking Up to the Left
Suppose IDs are in column B and names are in column A, to the left. VLOOKUP could not do this without rearranging columns.
With XLOOKUP you simply search column B and return column A. The function happily returns a value that sits left of the lookup column.
=XLOOKUP(D2, B2:B20, A2:A20)All lessons in this course
- The XLOOKUP Syntax
- Handling Misses With if_not_found
- Searching Left and From the Bottom
- Returning Whole Rows or Columns