How to Use VLOOKUP to Create a Search Box

A search box will allow you to quickly get the matching result when you have the identifier. For example, in the example above, you can have a person's salary in cell I3 if you type the person's ID in cell I2.

Step 1. Click the cell I3 where you want to have the Function (Download example);

Step 2. Click the "Formula" Tab from Ribbon;

Step 3: Click "Insert Function";

Step 4: In the Insert Function Window, type VLOOKUP in the box and click Go. VLOOKUP function will appear in the box below. Click VLOOKUP and then click OK at the bottom;

Step 5: The Function Arguments will appear. In the 1st box, click the cell with the lookup value I2;

Step 5. In the 2nd box, select the data range $A$1:$F$54 where the lookup value is located; the advantage to use absolute value is that the data range will be the same all the time.

Step 6. In the 3rd box, type in number 6 (column) for Salary (or 2 for First Name, 3 for the Last Name, 4 for DOB, 5 for Region);

Step 7. In the 4th box, type "false" for exact search (or true for approximate search), click "OK" at the bottom;

Step 8. Type any ID in cell I2, for example, 700356, salary $79,027 will appear in I3 automatically. If the value cannot find in column A, the result will return #N/A.

Download Vlookup Search Box Example

Leave a Reply