Entering the MATCH Function as the Column Index Number Argument
Normally VLOOKUP only returns data from one column of a data table and this column is set by the column index number argument.
However, in this example we have three columns that we wish to find data in so we need a way to easily change the column index number without editing our lookup formula.
This is where the MATCH function comes into play. It will allow us to match a column number to the field name - either January, February, or March - that we type into cell E2 of the worksheet.
The MATCH function, therefore, acts as VLOOKUP's column index number argument.
This is accomplished by nesting the MATCH function inside of VLOOKUP in the Col_index_num line of the dialog box.
Entering the MATCH Function Manually
When nesting functions, Excel doesn't allow us to open the second function's dialog box to enter its arguments.
The MATCH function, therefore, must be entered manually in Col_index_num line.
When entering functions manually, each of the function's arguments must be separated by a comma " , ".
Entering the MATCH Function's Lookup_value Argument
The first step in entering the nested MATCH function is to enter the Lookup_value argument.
The Lookup_value will be the location or cell reference for the search term we want to match in the database.
- In the VLOOKUP function dialog box, click on the Col_index_num line.
- Type the function name match followed by an open round bracket " ( "
- Click on cell E2 to enter that cell reference into the dialog box.
- Type a comma " , " after the cell reference E3 to complete the entry of the MATCH function's Lookup_value argument.
- Leave the VLOOKUP function dialog box open for the next step in the tutorial.
In the last step of the tutorial the Lookup_values will be entered into cells D2 and E2 of the worksheet.