This function has four parameter parts and is written as =VLOOKUP(para1, para2, para3, para4)
para1: the field you wish to use as the lookup value. Should be an entered value or a single cell
para2: the spreadsheet are that may/may not contain the value for which you are searching. This is most often a series of 2 or more columns. Only the first column of the designated area will be searched for a match
para3: the index of the value you wish to return. Most often a single column.
para4: a true or false boolean. If you want to only return an exact match enter FALSE otherwise enter TRUE or leave it blank to return anything that is similar.
As an example, I am using a list of customers with addresses and phone number. This data is contained in a customer listing spreadsheet.
In a new spreadsheet I have a shorter list of customer that I wish to return only the phone number for any customer that exists in the customer listing.
In cell B2 I have entered this formula =VLOOKUP(A2, 'CustomerListing'!B:G, 6, FALSE).
This formula is translated as follows:
Lookup the value in cell A2 from spreadsheet named CustomerListing. Look only in column B of CustomerListing for the value in cell A2 of the current spreadsheet. If a value is found return the value for the found row in column G. Why G? Because the index set in the 3rd parameter is 6. This tells the formula to move 6 columns from the first column to for the return value. B+6 = G. Lastly the formula says to only return exact matches to cell A2 because False has been entered as the last parameter. The formula will stop evaluating after finding the first match.
If no match is found for the specified value, N/A will be returned.
To finish the lookups for the remaining customers in the new list the formula can be copied down for the other cells in column B. (NOTE: a quick way to do this copy is to double-click the small square in the lower right corner of the cell you wish to copy the formula for. This will copy the formula from this cell to all empty cell going down until no value is found to the left)
I use the vlookup formula not only to move data across spreadsheets but most often to determine if a record exists in a spreadsheet.
Happy Exceling!!!
