VLOOKUP is a function that searches for the value you specify. The result returns a matching value from another column. To be more technical, you choose a range, and VLOOKUP looks up the value in the first column. The result it returns is a value from the same row of another column.
VLOOKUP stands for 'Vertical Lookup'. It is a function that makes Excel search for a certain value in a column (the so called 'table array'), in order to return a value from a different column in the same row.
The VLOOKUP function consists of three required arguments, in the following order: lookup value, table array, and column index number. The lookup value is the value for which you want to find matching data and must appear in the first column of the lookup table; it can be a value, a text string, or a cell reference.
The VLOOKUP function is a premade function in Excel, which allows searches across columns. Note: The column which holds the data used to lookup must always be to the left.
When to use the VLOOKUP Function in Excel? VLOOKUP function is best suited for situations when you are looking for a matching data point in a column, and when the matching data point is found, you go to the right in that row and fetch a value from a cell which is a specified number of columns to the right.
The function accepts four arguments: lookup_value, table_array, col_index_num, and range_lookup. The first three are mandatory arguments, while the last one is optional.
The main difference between VLOOKUP and LOOKUP functions is the VLOOKUP is limited to vertical lookups only and the LOOKUP function has cross functionality which means that it can perform both vertical lookups and horizontal lookups.
It can not lookup and return a value which is to the left of the lookup value. It works only with data which is arranged vertically. VLOOKUP would give a wrong result if you add/delete a new column in your data (as the column number value now refers to the wrong column).
Compare two columns in different Excel sheets using VLOOKUP
The best practice is to start typing the formula in your main sheet, then switch to the other worksheet and select the list using the mouse - an appropriate range reference will be added to the formula automatically.
In Excel, you can compare two columns using the IF condition. The formula to compare two columns is =IF(A2=B2,”Match”,” ”). It returns the result as Match against the rows that contain matching values, and the remaining rows are left empty.
A pivot table is a summary of your data, packaged in a chart that lets you report on and explore trends based on your information. Pivot tables are particularly useful if you have long rows or columns that hold values you need to track the sums of and easily compare to one another.
In the above examples, we used INDEX MATCH as the replacement for classic VLOOKUP to return a value from a predefined one-column range.
Interestingly, there are two ways you can use the VLOOKUP function; exact match and approximate match.
Array form: We strongly recommend using VLOOKUP or HLOOKUP instead of the array form.
As the name specifies, VLOOKUP is a built-in Excel function that helps you look for a specified value by searching for it vertically across the sheet. VLOOKUP in Excel may sound complicated, but you will find out that it is a very easy and useful tool once you try it.
VLOOKUP Cannot Look to its Left
A limitation of the VLOOKUP function is that it cannot look to its left. It will look down the leftmost column of a table and return information from the right.
Best Way to Vlookup When Lookup Value Is Not in the First Column. Instead of using the VLOOKUP function, you can use the INDEX & MATCH functions to look up vertically across columns. It doesn't require to be the lookup value in the first column of the lookup array.
Open the workbooks you want to compare. Go to the View tab, Window group, and click the View Side by Side button. That's it!