Few examples of VLOOKUP

 

=VLOOKUP(lookup value, range containing the lookup value, the column number in the range containing the return value, Approximate match (TRUE) or Exact match (FALSE)).

Here are a few examples of VLOOKUP:

Example 1

=VLOOKUP (B3,B2:E7,2,FALSE) VLOOKUP looks for Fontana in the first column (column B) in the table_array B2:E7, and returns Olivier from the second column (column C) of the table_array. False returns an exact match.

Example 2

=VLOOKUP (102,A2:C7,2,FALSE) VLOOKUP looks for an exact match (FALSE) of the last name for 102 (lookup_value) in the second column (column B) in the A2:C7 range, and returns Fontana.

Example 3

=IF(VLOOKUP(103,A1:E7,2,FALSE)="Souse","Located","Not found") IF checks to see if VLOOKUP returns Sousa as the last name of employee correspoinding to 103 (lookup_value) in A1:E7 (table_array). Because the last name corresponding to 103 is Leal, the IF condition is false, and Not Found is displayed.

Example 4

=INT(YEARFRAC(DATE(2014,6,30),VLOOKUP(105,A2:E7,5,FLASE),1)) VLOOKUP looks for the birth date of the employee corresponding to 109 (lookup_value) in the A2:E7 range (table_array), and returns 03/04/1955. Then, YEARFRAC subtracts this birth date from 2014/6/30 and returns a value, which is then converted by INY to the integer 59.

Example 5

IF(ISNA(VLOOKUP(105,A2:E7,2,FLASE))=TRUE,"Employee not found",VLOOKUP(105,A2:E7,2,FALSE)) IF checks to see if VLOOKUP returns a value for last name from column B for 105 (lookup_value). If VLOOKUP finds a last name, then IF will display the last name, otherwise IF returns Employee not found. ISNA makes sure that if VLOOKUP returns #N/A, then the error is replaced by Employee not found, instead of #N/A. In this example, the return value is Burke, which is the last name corresponding to 105.

Comments

Post a Comment

Popular posts from this blog

Vyapar App’s Complete Feature List

Image
  Vyapar App’s Complete Feature List: All About the BEST Billing Software for Small Businesses in India by  Vyapar November 22, 2019 Vyapar is a GST Business Accounting Software .  With Vyapar App, you can  make & share Invoices on WhatsApp, manage stocks/Inventory ,  make estimate bills, generate GSTR reports, t rack unpaid invoices, send payment reminders and collect payments directly online using UPI payments.  It has other features like business expense tracking , daily sale purchase record, shows profit and loss report. Here’s how this GST Billing App looks like: The main left menu in the Vyapar App has the following options:  Business Dashboard, Reports, Sale, Purchase, Expense, Other Income, Cash & bank, My Online Store, Settings, Backup/Restore, Utilities, Rewards, Help & Support . By tapping the top-left corner of the App (blue background), you can add your  company details  like name, company phone number, company addres...
Read more

Zoho Books Welcome Guide

Image
  Zoho Books Welcome Guide Navigating Zoho Books We know you're in a hurry! To make it easier for you, we've put together the shortest route to navigate your way around Zoho Books. Before we get into the specifics, see where you'll find what. 1.  The  Navigation Panel  on the left-hand side lists all the modules of Zoho Books. 2.  Get into the  Sales  tab and the  Purchases  tab to manage Estimates, Invoices, Expenses, Bills, and more. 3.  The  Quick Create  button by the respective modules is the shortcut to create new transactions. 4.  The  Quick Create  option can be used to create a transaction on the fly from anywhere in Zoho Books. 5.  Clicking on the  Recent Activities  option will display of list of most recent transactions you have carried out in your organization. 6.  Use the  Search  bar to quickly find a specific transaction, contact, item or more. 7.  The  Noti...
Read more

VLOOKUP function

Image
 VLOOKUP function Show All Hide All This article describes the formula syntax and usage of the VLOOKUP function  (function: A prewritten formula that takes a value or values, performs an operation, and returns a value or values. Use functions to simplify and shorten formulas on a worksheet, especially those that perform lengthy or complex calculations.) in Microsoft Excel. Description You can use the VLOOKUP function to search the first column of a range  (range: Two or more cells on a sheet. The cells in a range can be adjacent or nonadjacent.) of cells, and then return a value from any cell on the same row of the range. For example, suppose that you have a list of employees contained in the range A2:C10. The employees' ID numbers are stored in the first column of the range, as shown in the following illustration. If you know the employee's ID number, you can use the VLOOKUP function to return either the department or the name of that employee. To...
Read more