Use VLOOKUP to Find Values from an Excel® Table

Let’s say your organization receives donor money to support various charitable projects. You need to know how much money a given donor gave you last year. However, there are thousands of donors in your system, and because each donor record contains dozens of pieces of information (name, address, phone number, preferred projects, donation history, etc.), scrolling through pages of information to find the data would take hours.

VLOOKUP allows you search for that data quickly. The VLOOKUP searches through one column for a value – such as the donor’s name – and returns the value of the information you need. A VLOOKUP for donor Mark Smith that returns the donation column data saves you valuable time with an immediate figure. No more scrolling through hundreds of lines for the name!

Conduct a VLOOKUP Query

In this example, a donor lookup field is created in which a donor’s name can be entered and the cell containing the VLOOKUP formula returns last year’s total donations for that member.

1. Open a worksheet in which you want to create a VLOOKUP field.

2. Create a label field. This is the lookup_value field and where the donor’s name will be entered. It is highlighted green for clarity in this example. The lookup_value field is E2, and the VLOOKUP will reside in F2.

3. Enter a starting value in the field that is referenced by the VLOOKUP function. It should be a value near the top of the data list. In this case, Potter was entered.

4. Select the cell where the VLOOKUP formula will reside. In this example, that is cell F2.

5. In the Formula bar, type =VLOOKUP(

6. Complete the formula as prompted. This helpful tool that shows you the full formula and its variables is called the Formula Autocomplete Tooltip. The components to the VLOOKUP formula and what they represent are detailed below

• The lookup_value is the cell where the searched term is entered. In this example, cell E2 is where the donor’s name is entered.
• The table_array should be the area of data that is searched for the lookup value and the returned value (in this case the donation amount). In this example, that is cells B2 (the top value-containing cell in the Donor column) through C18 (the bottom value-containing cell in the Donation column).
• The col_index_num is the number of the column in the selected array searched for the returned value. Column C is the one searched, and while it is Column 3 in your table (counting from left to right), it is only column 2 of the array you identified for the VLOOKUP. 2 (for Column 2) is entered here.
• The range_lookup value is either TRUE or FALSE. Entering a value of TRUE requires the searched column (the donor column in this example) to be in ascending order, and causes Excel to search for an approximate match. A selection of FALSE eliminates the ascending order requirement but searches only for an exact match. In this example, FALSE is selected.

7. Close the parentheses in the formula.

8. Press the ENTER key on your keyboard.

9. Review the result to check your work. =VLOOKUP(E2,B2:C18,2,FALSE)

Practice makes perfect. Try this out on your own and see how quickly you can access the information you need. Just make sure that if you plan to apply your VLOOKUP formula to multiple cells, then your table arguments are absolute references.