Retrieving Data with the VLOOKUP Array Formula
Once the VLOOKUP array formula has been created and copied to the required cells it can be used to retrieve information from the database.
To do so, type the name of the item you wish to retrieve into the Lookup_value cell and press the ENTER key on the keyboard. Once done, each cell containing the VLOOKUP function should contain a different piece of data about the item you were searching for.
VLOOKUP uses the Column Index Number to determine which item of data should go into each cell.
You should recall that in a VLOOKUP array formula the Column Index Number contains multiple numbers. The first number in the Column Index Number tells VLOOKUP which column in the database to search for the first item of data. This data is placed in the first cell where VLOOKUP is located.
The next number in the Column Index Number indicates the column where the second piece of data is located. This data is placed in the second VLOOKUP cell. The process continues until all numbers in the Column Index Number have been utilized.
Tutorial Steps
- Click on cell D2 in your spreadsheet.
- Type Widget into cell D2 and press the ENTER key on the keyboard.
- The following information should be displayed in cells E2 to G2:
- E2 - $14.76 - the price of a widget
- F2 - PN-98769 - the part number for a widget
- G2 - Widgets Inc. - the name of the supplier for widgets
- Test the VLOOKUP array formula further by typing the name of other parts into cell D2 and observing the results in cells E2 to G2.
This completes the tutorial on creating and using a VLOOKUP array formula.
