This Excel tutorial builds on the VLOOKUP tutorial.
The MATCH function is used to determine the column number for the VLOOKUP. The MATCH function is:
= MATCH(value,array,[match_type])
The inputs are defined as:
value – the value to search for in the array
array – The range of cells that contains the search value.
match_type – the values inputted for match_type are 1,0,-1.
- One (1) is the default match type and finds the largest value that is less than or equal to the value.
- Zero (0) will find the first value that is equal to the value.
- Negative one (-1) will return the smallest value that is greater than or equal to the value.
In the given example we have Table 1 with three columns of data; Month, Total Sales and Products Sold. In this case we will be searching by Month and then choose the column of data by searching for the title, “Total Sales” or “Products Sold”.
In Excel, the table(array) is searched by the VLOOKUP, while utilizing the MATCH function to determine the column number. The VLOOKUP and MATCH functions combined will be entered as shown here:
=VLOOKUP(lookup_value, table_array,MATCH(value,array,match_type),range_lookup)
The two search criteria are inputted by the following steps.
- The lookup_value is Cell B9 or April.
- The table_array is Table 1 or Cells B14:D20.
- The row_column_number is determined by the MATCH function.
- The MATCH value is the text of the column header (“Products Sold”) or D8.
- The MATCH array is the column header row or B14:D14.
- The MATCH match type is Zero(0) to find an exact match
-
The VLOOKUP range lookup is FALSE to find an exact match.
The function returns the Products Sold in April as 48 in Cell D9.
The combination of the VLOOKUP and MATCH functions can be used to populate forms from table values or search pricing on pricing tables.
Download Excel spreadsheet example here
If you are in South Florida, you may attend one of our Excel Live Courses.
