Creating the Array Formula
Before closing the dialog box we need to turn our nested function into an array formula.
An array formula is what allows it to search for multiple terms in the table of data. In this tutorial we are looking to match two terms : Widgets from column 1 and titanium from column 2.
Creating an array formula in Excel is done by pressing the CTRL, SHIFT, and ENTER keys on the keyboard at the same time.
The effect of pressing these keys together is to surround the function with curly braces: { } indicating that it is now an array formula.
Tutorial Steps
For help with this example click on the image above.
- With the completed dialog box still open from the previous step of this tutorial, press and hold down the CTRL and SHIFT keys on the keyboard then press and release the ENTER key.
- If done correctly, the dialog box will close and a #N/A error will appear in cell F3 - the cell where we entered the function.
- The #N/A error appears in cell F3 because cells D3 and E3 are blank. D3 and E3 are the cells where we told the function to find the Lookup_values in step 5 of the tutorial. Once data is added to these two cells , the error will be replaced by information from the database.
