5 Alternatives To VLOOKUP Function: How To Get Values To The Left Of VLOOKUP Range

How do you get the value from a column that is to the left of a VLOOKUP range?

SQL-C2108l: Buổi 4. convert, nume...

SQL-C2108l: Buổi 4. convert, numeric, where, in(), between, like, avg, max, min, top, join

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

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

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 = 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

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

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

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

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

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.