VLOOKUP to the left in Excel

Last Updated on 01/11/2024
Reading time: 3 minutes

Learn how to look up data to the left with VLOOKUP. Although VLOOKUP can't do this directly, there are two methods.

  1. Use INDEX and MATCH. This works in all Excel versions but requires complex formulas.
  2. Use XLOOKUP. XLOOKUP is an improved version of VLOOKUP that can return data from any column, left or right.

Why VLOOKUP Has Limits

The VLOOKUP function only retrieves data to the right of the search ID column. But sometimes, we can't rearrange the columns. For clarity in our example, we need to keep the country name in the first column.

Table population by country

So, how can you create a formula to return a value to the left of the search column?

How to do a lookup to the left with Excel

Method 1: The XLOOKUP function does the job easily

If you work with Excel Online or Excel 365, the easiest way to return a column on the left of the column of research is to use the XLOOKUP function.

  1. Select the value to research
  2. Select the column of research (only one column to select)
  3. Select the column to return whatever if it's on the right or the left of the research's column

=XLOOKUP(G3;tblCountry[ISO 2];tblCountry[COUNTRY])

XLOOKUP to the left

Method 2: Solution with INDEX and MATCH

If you don't work with Excel 365, there is another technique for returning data on the left. This technique uses the functions INDEX and MATCH together.

  • The INDEX function searches for an item in a table in the function of a position (position 1, 2, 3, ...).
  • The MATCH function returns the position of a value in a list.
  • Then, with this position in the range, INDEX will return the value

Step 1: The position with the MATCH function

The MATCH function returns the position of an item in a list. But let's write this function in a new cell to understand what this function will return.

  1. The first parameter of the function MATCH is the value we search (the ISO code)
  2. The second parameter is the column where we look for this value
  3. We finish the function with 0 or FALSE to indicate that we have performed the exact research. It's exactly like the last parameter of the function VLOOKUP.

=MATCH(G3,$B$2:$B$240,0)

Look at the result of this function for different values of countries. In each case, the function MATCH returns the position in the column B 😎😍👍

Position of the ID in the list

Step 2: Include the MATCH inside the INDEX function

Next, insert the result from the MATCH function as the second argument in the INDEX function. This lets us return a value located to the left of the ID 😉

  1. Write the INDEX function
  2. Select the range of cells with the countries (column A)
  3. Copy the MATCH function as second argument of the INDEX function

=INDEX($A$2:$A$233,MATCH($H$3;$B$2:$B$233,0))

Return a value located to the left with INDEX and MATCH

Leave a Reply

Your email address will not be published. Required fields are marked *

VLOOKUP to the left in Excel

Reading time: 3 minutes
Last Updated on 01/11/2024

Learn how to look up data to the left with VLOOKUP. Although VLOOKUP can't do this directly, there are two methods.

  1. Use INDEX and MATCH. This works in all Excel versions but requires complex formulas.
  2. Use XLOOKUP. XLOOKUP is an improved version of VLOOKUP that can return data from any column, left or right.

Why VLOOKUP Has Limits

The VLOOKUP function only retrieves data to the right of the search ID column. But sometimes, we can't rearrange the columns. For clarity in our example, we need to keep the country name in the first column.

Table population by country

So, how can you create a formula to return a value to the left of the search column?

How to do a lookup to the left with Excel

Method 1: The XLOOKUP function does the job easily

If you work with Excel Online or Excel 365, the easiest way to return a column on the left of the column of research is to use the XLOOKUP function.

  1. Select the value to research
  2. Select the column of research (only one column to select)
  3. Select the column to return whatever if it's on the right or the left of the research's column

=XLOOKUP(G3;tblCountry[ISO 2];tblCountry[COUNTRY])

XLOOKUP to the left

Method 2: Solution with INDEX and MATCH

If you don't work with Excel 365, there is another technique for returning data on the left. This technique uses the functions INDEX and MATCH together.

  • The INDEX function searches for an item in a table in the function of a position (position 1, 2, 3, ...).
  • The MATCH function returns the position of a value in a list.
  • Then, with this position in the range, INDEX will return the value

Step 1: The position with the MATCH function

The MATCH function returns the position of an item in a list. But let's write this function in a new cell to understand what this function will return.

  1. The first parameter of the function MATCH is the value we search (the ISO code)
  2. The second parameter is the column where we look for this value
  3. We finish the function with 0 or FALSE to indicate that we have performed the exact research. It's exactly like the last parameter of the function VLOOKUP.

=MATCH(G3,$B$2:$B$240,0)

Look at the result of this function for different values of countries. In each case, the function MATCH returns the position in the column B 😎😍👍

Position of the ID in the list

Step 2: Include the MATCH inside the INDEX function

Next, insert the result from the MATCH function as the second argument in the INDEX function. This lets us return a value located to the left of the ID 😉

  1. Write the INDEX function
  2. Select the range of cells with the countries (column A)
  3. Copy the MATCH function as second argument of the INDEX function

=INDEX($A$2:$A$233,MATCH($H$3;$B$2:$B$233,0))

Return a value located to the left with INDEX and MATCH

Leave a Reply

Your email address will not be published. Required fields are marked *