Posted October 18, 2023

Guest Author: James Ambros

In today’s article we’ll go over one of the first functions users learn during their spreadsheets journey! =VLOOKUP(), which stands for "Vertical Lookup," is used to search for a value in the leftmost column of a table or range and return a corresponding value from a specified column to the right. =VLOOKUP() is best used in instances where there are a limited number of columns in a spreadsheet.

In Figure 1, the user is trying to pull Matt’s sales figures for February. As such, the appropriate syntax for the =VLOOKUP() can be seen here:

• lookup_value: This is the value the user wants to find in the first column. This is where we put “Matt”. Its also worth noting that the lookup_value could be a specific cell in your spreadsheet.
• table_array: This is the range or table from which the user wants to retrieve data, including the first column with the names, which will be used for the lookup.
• col_index_num: It specifies which column from the table the user wants to return the value from.
• range_lookup: This is optional. If TRUE or omitted, the formula will look for an approximate match. If FALSE, the formula will look for an exact match. Users will most likely use FALSE here!

Another example of a lookup function new spreadsheet users might find useful is =XLOOKUP()! The great thing about a =XLOOKUP() is that it is not limited to searching values in the leftmost column like in the =VLOOKUP(). In Figure 2, the user is still trying to pull Matt’s sales figures for February, but we are unable to use a =VLOOKUP() because the search value is not in the leftmost column. As such, we can use an =XLOOKUP(), of which the syntax can be seen here:

• lookup_value: This is the value the user wants to find. This is where we put “Matt”
• lookup_array: This is the array on which we are searching for the location of the lookup_value
• return_array: This is the array on which we want to return the value in the corresponding location of the lookup_value

The primary limitation with =VLOOKUP() & XLOOKUP() comes in the col_index_num and return_array fields, respectively. In Figure 1, we are able to easily count that February data is in the third column of the table. And in Figure 2, we are able to identify as part of the formula which column we want to return. But what happens if our data has too many columns to count manually? That's where =INDEX(MATCH(),MATCH()) comes in!

=INDEX(MATCH(),MATCH()) is a more versatile formula that uses two separate functions, =INDEX() and =MATCH(), to look up and retrieve data. In Figure 3, the user is trying to pull James’s sales figures for March. As such, the appropriate syntax for the =INDEX(MATCH(),MATCH()) can be seen here:

• array: This is the range or table the user wants to retrieve data from
• row_num: This is the vertical row number that the user wants to search, in this function the user is using the =MATCH() formula to identify that row number.

1. lookup_value: This is the value the user wants to find in the column they are searching. This is where they can reference the cell B8 which contains “James”.
2. lookup_array: This is the range where the user wants to search for the lookup_value.
3. match_type: This is optional, usually set to 0 for an exact match.

• col_num: This is the horizontal column number that the user wants to search, in this function the user is using the =MATCH() formula to identify that column number.

1. lookup_value: This is the value the user wants to find in the row they are searching. This is where they can reference the cell C8 which contains “3/31/2023”
2. lookup_array: This is the range where the user wants to search for the lookup_value
3. match_type: This is optional, usually set to 0 for an exact match

While =VLOOKUP() and =XLOOKUP() are fantastic tools for basic data retrieval tasks, =INDEX(MATCH(),MATCH()) provides more flexibility and power, making it the preferred choice for more complicated lookups and larger data sets. Ultimately, the choice between =VLOOKUP() and =INDEX(MATCH(),MATCH()) depends on the specific requirements of the user. Understanding when to use each function will help you work more efficiently and accurately with your spreadsheet data.