How do you get the value from a column that is to the left of a
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 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 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
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
SUMIFS function can take other criteria such as greater than or less than.
How To Use
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
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
How To Use
VLOOKUP Replacement (Excel)
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
How To Use
VLOOKUP Replacement (Google Sheets)
XLOOKUP formula is not available in Google Sheets another alternative you could use is the powerful
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.
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