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:- 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.
- Only the first value will be chosen if the lookup column has duplicate entries.
- There is no case distinction in VLOOKUP.
- 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.
