Skip to main content

The Most Popular and useful formula in Excel


MS Excel: VLOOKUP Function (WS)

In Microsoft Excel, the VLOOKUP function searches for value in the left-most column of table_array and returns the value in the same row based on the index_number.

Syntax

The syntax for the VLOOKUP function is:
VLOOKUP( value, table_array, index_number, [not_exact_match] )
value is the value to search for in the first column of the table_array.
table_array is two or more columns of data that is sorted in ascending order.
index_number is the column number in table_array from which the matching value must be returned. The first column is 1.
not_exact_match is optional. It determines if you are looking for an exact match based on value. Enter FALSE to find an exact match. Enter TRUE to find an approximate match, which means that if an exact match if not found, then the VLOOKUP function will look for the next largest value that is less than value. If this parameter is omitted, the VLOOKUP function returns an approximate match.

Note

  • If index_number is less than 1, the VLOOKUP function will return #VALUE!.
  • If index_number is greater than the number of columns in table_array, the VLOOKUP function will return #REF!.
  • If you enter FALSE for the not_exact_match parameter and no exact match is found, then the VLOOKUP function will return #N/A.

Applies To

  • Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

Type of Function

  • Worksheet function (WS)

Worksheet Function Example

Let's take a look at an example to see how you would use the VLOOKUP function in a worksheet:
Microsoft Excel
Based on the Excel spreadsheet above, the VLOOKUP function would return the following:
=VLOOKUP(10251, A1:B6, 2, FALSE)would return "Pears"
=VLOOKUP(10251, A1:C6, 3, FALSE)would return $18.60
=VLOOKUP(10248, A1:B6, 2, FALSE)would return #N/A
=VLOOKUP(10248, A1:B6, 2, TRUE)would return "Apples"
=VLOOKUP(10248, A1:B6, 2)would return "Apples"

Frequently Asked Questions

Question: In Microsoft Excel, I'm using the VLOOKUP function to return a value. I want to sum the results of the VLOOKUP, but I can't because the VLOOKUP returns a #N/A error if no match is found. How can I sum the results when there are instances of #N/A in it?
Answer: To perform mathematical operations on your VLOOKUP results, you need to replace the #N/A error with a 0 value (or something similar). This can be done with a formula that utilizes a combination of the VLOOKUP function, IF function, and ISNA function.
Labels:

Comments

Post a Comment

Popular posts from this blog

Password Protect your USB Flash Drive without any software....

I was trying to protect my USB flash drive from a long time but didn’t find anything nice. Almost all the software were either not giving full features or were asking for money to do so and give full benefits. One software called Truecrypt was nice but not giving me simple password protection feature. Anyways, now I submit one good info with all who want to simply password protect their USB flash drive / pen drive without all unnecessary softwares. Steps to protect your USB flash drive 1.       Insert your pendrive. 2.       Open the drive 3.       Create a folder (lets name is ABCD) and keep all your data in it. (You can partially protect this disk too by moving your important / secure data only into this folder) 4.       Remember a password. Lets name is 12345 5.       Now open a note pad and copy and paste the following text into it: -...
10 comments
Read more

How to Calculate Detention of Vehicles considering the complex Date & Time in excel

Some times it is difficult to calculate some cases which deals with Date & Time and calculating the Detention charges is one of them. Please download the excel file (and do remember that it has been prepared on the basis the following basis.... which can be changed with the help of formulas) https://drive.google.com/file/d/0Bw1qNoQ8YzHZY3E5VVl1SUduWm8/view?usp=sharing
2 comments
Read more

Difference Between Consignment And Sales

Consignment can not be treated as sales of goods. It is different from sales. The difference between consignment and sales are as follows: 1. Ownership Consignment: The ownership of the goods remains with the consignor until sales is effected by the consignee. Sales: The ownership of the goods immediately transferred to the buyer when sale is effected. 2. Relationship Consignment: The relationship between the consignor and consignee are of principal and agent. Their relation ship are continued till terminated. Sales: The relationship between the two parties are that of seller and buyer and they terminated as soon as payment is made and goods are delivered. 3. Expenses Consignment: The expenses incurred by the consignee to execute sale and the expenses incurred by consignor to send the goods to the consignee, both are borne by the consignor. Sales: Any expenses incurred after the sale is not borne by the seller. 4. Risk Consignment: The risk of goods under c...
8 comments
Read more