How do you get the value from a column that is to the left of a `VLOOKUP`

range?

The `VLOOKUP`

function is a powerful function that enables you to capture the value from a range, provided the value is to the *right* of the lookup range.

For example, suppose you have the following data in your spreadsheet that contains the employee ID in column A, the name of the employee in column B and their hire date in column C. You cannot search by column B to return column A using the `VLOOKUP`

function.

A | B | C | |
---|---|---|---|

1 | 1001 | John Smith | 12/01/2022 |

2 | 1002 | Jane Doe | 04/02/2022 |

`VLOOKUP`

works great when the data is structured to the right of your searching column, but when it isn’t it’s not the best function to use. So what do you do?

Here are 3 alternatives to the VLOOKUP function:

## How To Use `SUMIFS`

As `VLOOKUP`

Replacement

The `SUMIFS`

function takes at least parameters, with the first parameter representing the `sum_range`

and the next two parameters being pairs of `criteria_range`

(that needs to be the same length as `sum_range`

) and `criterion`

.

Using the same data above, to return column A by using column B as the search your formula would look like this:

`=SUMIFS(A:A, B:B, "Jane Doe")`

This formula states: fetch the results from column A where column B equals `"Jane Doe"`

. The result from this formula would be `1002`

.

The `SUMIFS`

function can take other criteria such as greater than or less than.

## How To Use `MATCH`

And `INDEX`

As `VLOOKUP`

Replacement

The `MATCH`

function takes at least 2 parameters with the first being the value to search (`search_value`

) and the second being the range to search in. The optional third parameter helps determine the type of match sought with a value of `1`

being to capture either the first value less than or equal to the `search_value`

or when the first value is greater than or equal to the `search_value`

(-1), with `0`

in this parameter meaning to do an exact match.

If the formula looked like this on our simple data set you would get the result of `2`

:

`=MATCH("Jane Doe",B:B)`

To produce the result of obtaining column A you could combine this with the `INDEX`

function which takes at least two parameters, with the first representing the `range`

and the second being the row to return. The optional third parameter being the column if the `range`

is larger than one column.

The formula for the `INDEX`

function combined with the `MATCH`

formula would be as follows:

`=INDEX(A:A, MATCH("Jane Doe", B:B))`

This would produce the intended result of `1002`

.

## How To Use `XLOOKUP`

As `VLOOKUP`

Replacement (Excel)

The `XLOOKUP`

function in Excel works in a similar way to the previous two functions above. The `XLOOKUP`

function contains 6 parameters with the first 3 mandatory and represents the following: the first is the value to look up, the second is the range where the value can be found, the third is the range (of the same size) of what you want to be returned. The other 3 parameters are optional with the 4th parameter allowing the user to set a default value if the search cannot be found, the 5th parameter determining the match type (like the `MATCH`

function above), the 6th parameter setting the search type (1 = first-to-last; -1 = last-to-first; 2 binary search (ascending); -2 binary search (descending)).

Using the common example in the post the `XLOOKUP`

function would look like the following:

`=XLOOKUP("Jane Doe", B:B, A:A)`

This formula also produces the intended result of `1002`

.

## How To Use `QUERY`

As `VLOOKUP`

Replacement (Google Sheets)

As the `XLOOKUP`

formula is not available in Google Sheets another alternative you could use is the powerful `QUERY`

function.

The `QUERY`

function takes at least 2 parameters: the first being the range of data and the second the query to perform. The optional third parameter sets the number of header rows.

To extract the data from the range using the `QUERY`

function you could write the following formula:

`=QUERY(A1:C2,"SELECT A WHERE B='Jane Doe'")`

This also outputs the intended result of 1002.

## Summary

There are many other alternatives to the `VLOOKUP`

function in both Excel and Google Sheets. If you cannot change the structure of the data range easily enough to continue using the `VLOOKUP`

formula then look at using some alternative formulas such as `SUMIFS`

, `INDEX(MATCH())`

, `XLOOKUP`

and `QUERY`

.