How to use VLOOKUP function used in spreadsheet?

VLOOKUP function can be used to extact relevant content from spreadsheets. Syntax: VLOOKUP(SearchCriterion; Array; Index; SortOrder). To understand the concept easily,consider the following example:-

There are two sheets that go by the name of contacts and leads and in contacts there is column A(email id) and in leads there are two columns, namely, F(email id) and G(phone number).

In order to extract the phone numbers corresponding to each and every mail id in contacts from leads all you have to do is to just apply the VLOOKUP formula in a column adjacent to column A in contacts(say B).

Before applying the formula we have to understand the basic working of the formula.

• SearchCriterion is the value that is being searched for.
• Array should have atleast 2 columns.
• Index indicates the number of the column that contains the value to be returned.
• SortOrder indicates whether the first column in the array is sorted in ascending order.

The formula when applied is as follows:-

=VLOOKUP(A2,leads.F$2:G$21653,2,0)

A2 indicates the mail id in the second row of the contacts sheet which is taken as reference for comparing with leads.

leads.F$2:G$21653 is for selecting the range from F2 to G21653 from the leads sheet and the value '2' is used to exact the content from the second column in the array and '$' symbol is used to change cell reference to absolute reference and here it is applied before the row reference to prevent the row from changing while being copied.

Entering a nonexistent mail id displays the phone number with the next mail id down. To prevent this, enter FALSE or the value 0 as the last parameter in the formula so that an error message is generated when a nonexistent mail id is entered.