Did you know VLOOKUP can’t return data from the left? Discover two simple methods to solve this problem using XLOOKUP or the INDEX and MATCH functions.

1. Use INDEX and MATCH. This method works in all Excel versions but requires more 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, rearranging columns is not an option. For example, we may need to keep the country name in the first column for clarity.

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

• TIP: Did you know Excel can return the population of any country in the world? That’s the Data Type 💡

Method 1: The XLOOKUP Function

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

1. Select the value to search for, here the cell C11.
2. Select the column to search in, the ISO Code column or Column C
3. Select the column to return, here, the Capital column or Column B

Because XLOOKUP only requires the lookup and result columns, their positions in the original table don’t matter 😉

Method 2: INDEX and MATCH Solution

If you don’t have Excel 365, you can use another technique to return data on the left using INDEX and MATCH.

• The INDEX function returns an item in a table based on its position.
• The MATCH function finds the position of a value in a list.
• Use this position to make INDEX return the value.

Step 1: Use the MATCH Function

The MATCH function finds an item’s position in a list. Write the function in a new cell to see what it returns.

1. First parameter: the value to search for (e.g., the ISO code)
2. Second parameter: the column to search in
3. Use 0 or FALSE for exact matches, like the last parameter of VLOOKUP.

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

See the results of the MATCH function for different country values. It returns the position in column B 😎😍👍

Step 2: Combine MATCH with INDEX

Next, use the result of the MATCH function as the second argument in the INDEX function. This lets you return a value on the left of the ID 😉

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

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

Related Articles

• Function XLOOKUP in Excel
• Why VLOOKUP Returns #N/A Error
• How to do a VLOOKUP on 2 columns
• How to use the INDEX function in Excel?