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
.