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 set
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 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 set
As 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
= firsttolast;
1
= lasttofirst;
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 set
As 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 databaselike 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 set
As 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 set
Using 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 order
Therefore, 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.