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.
