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

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

Tally Trial Balance Consolidation Utility in excel

Hello Friends, Financial Year has ended recently and many of the accounts & finance professionals are ready for preparing the Balance Sheets. Many companies have operations at multiple locations and they prepare the accounts accordingly and at the end consolidate the data and prepare the Final Accounts. Normally the trial balances of each locations are consolidated and on that basis the Profit & Loss Accounts & Balance Sheets are prepared. Sometimes, the consolidation of Trial Balance is lengthy, tricky and difficult part for the accounts professionals although its a simple process of summarizing the transactions. So I am sharing an excel utility where upto 10 nos of Trial Balances (Tally Generated) can be consolidated within a minute. The Excel utility is self explanatory, but some basic points are mentioned here: - 1. You need to export Group Names of each locations from tally and paste in this file. 2. You need to export Trial Balance of each location from...
Post a Comment
Read more

Sai Sampurna Padhi - A Sweet Bubbly Girl fighting for her life at Cuttack

Hello Everyone, Today I want to share with you all about a girl named "Sai Sampurna Padhi" Some details of this girl: - Name                   : - Sai Sampurna Padhi Age                       : - 2.5 Years Residence           : - Bidanasi, Cuttack, Odisha Father's Name    : - Linku Padhi Mother's Name   : - Chandrika Mohapatra Father's Occupation    : - Auto Driver at Badambadi Bus Stand, Cuttack This girl is admitted to SCB Medical College because she is suffering from a very dangerous disease called "Blood Cancer" and as its at Stage-I doctors has prescribed to go for Bone Marrow transplant as soon as possible so that this can be cured. But the operation cost around 20-25 Lacks which they can't arrange. For the time being, every week she has need of blood, plasma, test & medicine which is less as it is a government ho...
Post a Comment
Read more