Left lookup in Excel using XLOOKUP

Learn how to easily perform a left lookup with the XLOOKUP function instead of using the VLOOKUP or INDEX and MATCH functions.

We already know that XLOOKUP has opened new horizons in Excel. Today’s guide will be on how to replace the VLOOKUP provides limited functionality in case of left lookup.

How the left lookup works

In the example, we want to find the correspondent item in the Location column if the value in the Sales column is 8932.

left lookup example

First, we will create named ranges. This step will dramatically improve the formula’s readability. Select range B3:B11, locate the name box: type ‘Location‘, and press enter. After that, select range D3:D11 and add a name, in this example, ‘NetSales.’

Apply the formula that provides a left lookup:

=XLOOKUP(D6, NetSales, Location)

In the example, we are using the following arguments:

  • lookup_value: 8392
  • lookup_array: D3:D11 (or a named range, NetSales)
  • return_array: B3:B11 (or a named range, Location)
xlookup left

Tip: the formula mentioned above is equivalent to =XLOOKUP(D6, D3:D11, B3:B11)

Workaround with VLOOKUP (reverse lookup)

The most frustrating limitation of VLOOKUP is that the function lookup values to the right only. We need left lookup, so here is a workaround with VLOOKUP.

Using the CHOOSE function, we can rearrange the lookup table:

=CHOOSE({3,2,1}, B2:B11, C2:C211, D2:D11)

=CHOOSE({3,2,1}, Location, Orders, Sales)

structure

The CHOOSE function rearranges the columns and VLOOKUP using a left lookup.

Inside the formula, CHOOSE swaps columns 1 and 3 and creates an organized table ready for VLOOKUP.

The final formula:

=VLOOKUP(D6,CHOOSE({3,2,1},B3:B11,C3:C11,D3:D11),3,0)

reverse VLOOKUP

Here is the practice file:

Download the example!

Istvan Vozar

Istvan is the co-founder of Excelkid. He writes blog posts and helps people to reach the top in Excel.