Step by Step Guide to Vlookup Formula in Excel

Safalta Expert Published by: Saksham Chauhan Updated Thu, 08 Sep 2022 12:20 AM IST

Highlights

Check Step by Step Guide to Vlookup Formula in Excel At Safalta.com

Free Demo Classes

Register here for Free Demo Classes

Please fill the name
Please enter only 10 digit mobile number
Please select course
Please fill the email
Something went wrong!
Download App & Start Learning
Microsoft Excel is a very effective tool for managing data. It makes it simple for users to evaluate and comprehend data. Microsoft Excel is unquestionably a strong and extremely popular programme utilised by practically every business, even today, despite the fact that the variety of activities it allows the user to complete is sometimes underappreciated. Excel offers a wide variety of features that make working with data easier. One such Excel function is VLOOKUP.

Source: Safalta.com

VLOOKUP functions as a search tool by scanning vertically over a table or spreadsheet for certain data. Download these FREE Ebooks:
1. Introduction to Digital Marketing
2. Website Planning and Creation

 

What in Excel is VLOOKUP?

Vertical Lookup is referred to as VLOOKUP. VLOOKUP is a built-in Excel function that, as its name implies, enables you to find a certain number by looking for it vertically across the page. Excel's VLOOKUP function may appear difficult at first, but if you give it a try, you'll see that it's a simple and helpful tool.

How Can I Use VLOOKUP to Find an Exact Match?

It is simple to search for an exact match in the table with VLOOKUP. With the use of an example, let's look at how to achieve this:

The VLOOKUP function is used in the example below to retrieve the value of an exact match ID from the provided database. Therefore, we choose cell H5 as the first parameter's lookup value.

  • The second input contains a description of the table's location. The table's placement is A2:F11, as you can see.
  • The third input contains the integer for the Column Index. This informs us of the value that ought to be returned from the row we're searching up. The product column in the illustration is 3.
  • A Boolean Expression is used as the final parameter. For the VLOOKUP function to provide an exact match for the value in this case, the value is set to FALSE. In the event that the precise value cannot be discovered, a N/A error is shown.

VLOOKUP Formula

The VLOOKUP function has a straightforward syntax that we may use to access it. VLOOKUP's syntax is as follows:

VLOOKUP(lookup_value, table_array, col_index_number,[range_lookup])
  • lookup value: The value you wish to search for in our database is specified here.
  • This is where the values are located in Excel's table array.
  • col index number: This indicates the number of the column from which we should return the value.
  • range lookup: This has two choices; setting the value to FALSE indicates that we are seeking an exact match. If the value is TRUE, then a close match is what we're hoping for.

How Can I Use VLOOKUP To Find Approximate Matches?

In order to discover the closest value that is smaller than the lookup value that we give, Approximate Match searches for the next greatest value. The VLOOKUP function is used in the example below to get the RAM specifications of a laptop that costs 1300 EUR. We are also aware that the table does not include this data. Let's thus utilise the approximate match to locate the answer. The first column has to be sorted in ascending order. Otherwise, VLOOKUP will produce inaccurate results.
 

  • Copy the cost and RAM specifications to a new spot, then enter your lookup value. The lookup value in this case is $1300.


Next, choose your data range and then choose the ascending filter option to sort the values in the first column. The filter buttons will become active on your column headings after you select the filter choice.

Sort the pricing information by increasing price. Select OK.

Enter your VLOOKUP formula right now. The VLOOKUP value will be the first parameter. The table range is specified by the second parameter. We specify the column number in the third parameter to ensure that the data for that column are returned. The last argument is set to TRUE at this point. In doing so, the VLOOKUP function will be able to roughly match the value.


Press enter after typing the formula. In this instance, 8GB are returned at a cost of $1300.

How Can I Use VLOOKUP With Multiple Criteria?

You can provide several criteria using a helper column that the VLOOKUP cannot handle naturally. In this example, we'll figure the cost depending on the vendor and the item. We utilise a helper column, for instance, to find up the cost of an Apple MacBook Pro when the business name and product name are in two separate columns. The values from both columns will be combined and stored in this column. To run a VLOOKUP with various criteria, follow the instructions below.

  • First, insert should be selected by right-clicking on a column header. You may put a column to the left of the Company column using this. Give it the name "Company & Product."
  • Enter the following formula when generating the helper column: =C2&"-"&D2. Drag the formula down to the remaining cells in the column after that.
  • We can now search for the value after successfully constructing the concatenated column. We are searching for the cost of an Apple MacBook Pro here. Therefore, we insert =VLOOKUP(B15&"-"&C15,B1:G11,5,FALSE) into the formula.

 
  • The lookup value, B15&"-" &C15, is concatenated with the help of the first argument. The concatenate formula, CONCATENATE, is another option (B15,"-",C15). The table range is the second argument. A value from that column is returned when the third argument, which specifies the column index, is used. We are seeking for an Exact Match, hence the last option is set to FALSE. Entering the key will return the pricing as seen below.

How to Effectively Use the VLOOKUP Function

The VLOOKUP function displays an error when we input an improper formula or add a bad value someplace. Here are some recommendations for utilising the VLOOKUP function:
  1. The VLOOKUP function will also assess non-exact matches if range lookup is left out. If an exact match is found in your data, it will be used.
  2. Only the first value will be chosen if the lookup column has duplicate entries.
  3. There is no case distinction in VLOOKUP.
  4. A mistake will appear if you add a column after using the VLOOKUP formula because hard-coded index values are not updated immediately when you add or remove columns.

Typical Mistakes in the VLOOKUP Function

  • #N/A! error – Occurs when the specified lookup value is not matched by the VLOOKUP function.
  • When the col index num input exceeds the number of columns in the table array provided, or when the formula tries to reference cells that don't exist, a #REF! error is generated.
  • #VALUE! error - Occurs if either: range lookup argument is not recognised as one of the logical values TRUE or FALSE; col index num argument is less than 1 or is not recognised as a numeric value.

Conclusion

An explanation of how to utilise Excel's VLOOKUP function for exact and approximative matches is provided in this article. Additionally, you now know how to utilise the VLOOKUP function with various criteria. The parameters that are required to carry out searching activities in Excel need to be well understood and remembered. We believe that through enhancing your Excel abilities, this article will pave the road for you to have a successful career. Please leave any questions in the comments box, and one of our specialists will respond to you as soon as possible.

Free Demo Classes

Register here for Free Demo Classes

Trending Courses

Master Certification in Digital Marketing  Programme (Batch-17)
Master Certification in Digital Marketing Programme (Batch-17)

Now at just ₹ 64999 ₹ 12500048% off

Professional Certification Programme in Digital Marketing (Batch-11)
Professional Certification Programme in Digital Marketing (Batch-11)

Now at just ₹ 49999 ₹ 9999950% off

Advanced Certification in Digital Marketing Online Programme (Batch-29)
Advanced Certification in Digital Marketing Online Programme (Batch-29)

Now at just ₹ 24999 ₹ 3599931% off

Advance Graphic Designing Course (Batch-12) : 100 Hours of Learning
Advance Graphic Designing Course (Batch-12) : 100 Hours of Learning

Now at just ₹ 19980 ₹ 3599944% off

Flipkart Hot Selling Course in 2024
Flipkart Hot Selling Course in 2024

Now at just ₹ 10000 ₹ 3000067% off

Advanced Certification in Digital Marketing Classroom Programme (Batch-3)
Advanced Certification in Digital Marketing Classroom Programme (Batch-3)

Now at just ₹ 29999 ₹ 9999970% off

Basic Digital Marketing Course (Batch-24): 50 Hours Live+ Recorded Classes!
Basic Digital Marketing Course (Batch-24): 50 Hours Live+ Recorded Classes!

Now at just ₹ 1499 ₹ 999985% off

WhatsApp Business Marketing Course
WhatsApp Business Marketing Course

Now at just ₹ 599 ₹ 159963% off

Advance Excel Course
Advance Excel Course

Now at just ₹ 2499 ₹ 800069% off