In its simplest form, the VLOOKUP function says: =VLOOKUP(What you want to look up, where you want to look for it, the column number in the range containing the value to return, return an Approximate or Exact match – indicated as 1/TRUE, or 0/FALSE).
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 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.
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 superior alternative to VLOOKUP is INDEX MATCH. While VLOOKUP works fine in most cases, it tends to reveal flaws when you start using it in large and complex sheets. The INDEX MATCH formula is actually two different functions; INDEX and MATCH.
The VLOOKUP function can only return one record. It will return the first record that matches the value you looked for. If your table contains duplicates then VLOOKUP will not be up to the task.
When you have two columns of data and want to find out which data points from one list exist in the other list, you can use the VLOOKUP function to compare the lists for common values.
Solution: If you are sure the relevant data exists in your spreadsheet and VLOOKUP is not catching it, take time to verify that the referenced cells don't have hidden spaces or non-printing characters. Also, ensure that the cells follow the correct data type.
Array form: We strongly recommend using VLOOKUP or HLOOKUP instead of the array form.
Interestingly, there are two ways you can use the VLOOKUP function; exact match and approximate match.
The XLOOKUP Function. The XLOOKUP function is an improved version of various Excel LOOKUP functions. Use it to search a specific range or an array of a specified value in a worksheet, both vertically and horizontally. And, it returns the corresponding value in another column.
Example: “VLOOKUP helps find data in a large spreadsheet using the lookup value in another spreadsheet. It is an essential function because it provides valuable insights and relationships between the data set.
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!
If the VLOOKUP function does not find an exact match, it will return the #N/A error. By using the IF and ISNA functions, you can return the Unit Price value if an exact match is found. Otherwise, a 0 value is returned. This allows you to perform mathematical operations on your VLOOKUP results.
If the VLOOKUP function cannot find a specified value, it throws an #N/A error. To catch that error and replace it with your own text, embed a Vlookup formula in the logical test of the IF function, like this: IF(ISNA(VLOOKUP(…)), "Not found", VLOOKUP(…))