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`

.

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

1 | 1001 | John Smith | 12/01/2022 | 1002`=SUMIFS(A:A,B:B,"Jane Doe")` |

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

`SUMIFS`

function to obtain a result from a data setThe `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 combination of functions allows for better matching with the third parameter of the `MATCH`

function allowing a lot more ability than other alternatives listed here.

Here’s how this would look in a spreadsheet:

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

1 | 1001 | John Smith | 12/01/2022 | 1002`=INDEX(A:A, MATCH("Jane Doe", B:B))` |

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

`INDEX`

and `MATCH`

functions to obtain a result from a data setAs you can see from the spreadsheet above, you achieve the same 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 three mandatory and represents the following: `lookup_value`

is the value to look up; `lookup_array`

is the range where the `lookup_value`

can be found; `return_array`

is the range (of the same size) of what you want to have returned.

The next three parameters are optional, with the 4th parameter `if_not_found`

allowing the user to set a default value if the search cannot be found; the 5th parameter `match_mode`

determining the match type (like the `MATCH`

function above); and the 6th parameter `search_mode`

setting the search type (`1`

= first-to-last; `-1`

= last-to-first; `2`

binary search (ascending); `-2`

binary search (descending)).

Using the same example in this post the `XLOOKUP`

function would look like the following:

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

The `XLOOKUP`

formula is very similar to the `VLOOKUP`

function and is easy for users to grasp due to the equivalent parameters in both functions.

Here’s how this would look in your Excel spreadsheet:

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

1 | 1001 | John Smith | 12/01/2022 | 1002`=XLOOKUP("Jane Doe", B:B, A:A)` |

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

`XLOOKUP`

function to obtain a result from a data setAs you can see from the above spreadsheet, the formula produces the same 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'")`

The `QUERY`

function in Google Sheets is a powerful function that is very different from the structure of the `VLOOKUP`

function. However, if you put in the time to learn this powerful function it will reap many rewards when working with database-like data sets.

Here’s how this looks in your Google Sheet:

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

1 | 1001 | John Smith | 12/01/2022 | 1002`=QUERY(A1:C2,` `"SELECT A WHERE B='Jane Doe'")` |

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

`XLOOKUP`

function to obtain a result from a data setAs you can see, this also produces the same result of 1002.

`LOOKUP`

As `VLOOKUP`

Replacement

The `LOOKUP`

function is the most common alternative when looking up a range that can be either side of the lookup range. The `LOOKUP`

function takes three parameters: `search_value`

, `search_range`

, and an optional third parameter `result_range`

.

If you’ve used `VLOOKUP`

before, you would identify with the first two parameters as they are somewhat the same in the `LOOKUP`

function. However, instead of the `search_range`

being the entire range, it would just be the range to search for the `search_value`

.

In the running example, the `search_range`

is all of column `B`

, to return a corresponding column to the left of the `search_range`

you would need to set the `result_range`

to a range of the same dimension in **height** that matches the `search_range`

. This would mean all of column `A`

.

Therefore, using the working example to use the `LOOKUP`

function as an alternative to the `VLOOKUP`

function would mean writing the formula like so:

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

Here’s what this formula produces in your spreadsheet:

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

1 | 1001 | John Smith | 12/01/2022 | 1002`=LOOKUP("Jane Doe",B:B,A:A` `)` |

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

`LOOKUP`

function to obtain a result from a data setUsing the `LOOKUP`

function can be an alternative to the `VLOOKUP`

**however**, there are a couple of things to be wary of when using the function.

### Why Is `LOOKUP`

Not Working?

There may be instances where the `LOOKUP`

function may be returning strange values. According to the `LOOKUP`

function documentation there are several assumptions made by this function which you should know as this may influence your decision on whether to use it or not as a viable alternative.

Firstly, if you’re using numbers in your `search_value`

and there is **no exact match** the `LOOKUP`

function will return the value that is immediately smaller in the `search_range`

.

Here’s an example where the value `1009`

cannot be found, and the `LOOKUP`

function returns the closest value *less than* this value:

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

1 | 1001 | John Smith | 12/01/2022 | Jane Doe`=LOOKUP(1009,A:A,B:B` `)` |

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

3 | 1010 | Harry Smith | 06/06/2022 |

`LOOKUP`

function will return the value smaller than `search_value`

The other problem with the `LOOKUP`

function is that it assumes the data in the `search_range`

is **sorted in ascending order**. If your data is not sorted you will get incorrect results.

Here’s an example where the values in column `A`

have been changed and the `LOOKUP`

function is searching for the value `1010`

but when the function hits `1020`

for `Jane Doe`

as it *assumes* the `search_range`

is sorted it returns the value of `John Smith`

:

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

1 | 1001 | John Smith | 12/01/2022 | John Smith`=LOOKUP(1010,A:A,B:B` `)` |

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

3 | 1010 | Harry Smith | 06/06/2022 |

`LOOKUP`

function assumes `search_range`

is in ascending orderTherefore, if using the `LOOKUP`

function as an alternative to the `VLOOKUP`

function, be mindful that your `search_range`

is sorted, and if your `search_value`

is not found (and the `search_value`

is a number), you may still get results.

## 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`

, `QUERY`

and `LOOKUP`

.

Each has its advantages and disadvantages. Make sure you’re aware of these issues before jumping in when seeking a result from your data set.