You can use the VLOOKUP function to get the salary and code of an employee. The process is case-sensitive by default and searches for the closest match. The first value found is returned as a result. There are many limitations to this function. It would help if you understood these limitations to use them properly.
Contents
show
Function’s limitations
The VLOOKUP function is a powerful tool but has some limitations. First, it doesn’t distinguish between uppercase and lowercase values. Hence, the data in the fifth column will be ignored if the VLOOKUP formula tries to look up “Jason” instead of “JASON.” This is because the VLOOKUP function was designed to search the data after the lookup column.
Another limitation of the VLOOKUP function is that it generates an error if the lookup value is not in the proper format. When this happens, the function returns an error with #NA (unavailable) meaning. Sometimes, the error is caused by leading or trailing spaces in the lookup value. Hence, if you want to use this function correctly, select the table_array first.
To overcome these limitations, Microsoft released a new function called XLOOKUP. It replaces the HLOOKUP and VLOOKUP functions in Excel and is available only in Office 365. In addition, XLOOKUP is more flexible and will eliminate the need to use VLOOKUP and HLOOKUP.
It’s case-sensitive by default.
Excel is case-sensitive by default, but you can change this behavior with a few keystrokes. For example, you can set a cell blank, placing the found value in that cell. You can also make your search case-sensitive by using an array formula. In this way, you can get the exact result you’re looking for.
To make your lookup case-sensitive, use the UPPER or LOWER function in the reference query and input. However, this may impact your package’s performance, and disabling the cache is not a good solution. In many cases, you’ll need to use Full Cache mode for performance reasons.
In addition, you can use an EXACT function to perform a case-sensitive lookup. This function requires a sorted array. You can also use a wildcard character to perform a partial search.
It searches for an approximate match.
If you want to search for a value in the first column of a table, you can use the VLOOKUP function. You can specify two or more columns. The table_array is the data array. You can specify whether you want the exact match or an approximate match. The default value is TRUE.
VLOOKUP searches for an approximate match using the logical input one or TRUE to compare two numbers. The function searches for the closest match and returns it. To do this, you must first sort the first column. The second column contains values in different alphabets. In the example above, you have a column called “letter grades” with an index of 87%. Then, enter the VLOOKUP formula in the formula bar, and press enters to get the results.
Sometimes, the specified data is unavailable in the data range. In this case, VLOOKUP is useful. For example, suppose you want to look up a unit price for quantity 58, but that value is not in the Quantity column. Then, you could use VLOOKUP to retrieve column B’s next closest unit price.
It returns the first value it finds
When a cell reference has more than one value, the VLOOKUP function returns the first value it finds. You can use the range lookup function if you need an exact match. Otherwise, you can use the INDEX function. It works the same way, except that you don’t have to specify a column.
In this example, the first column of the table contains the value of Milch, while the second column contains the value of a product. By specifying a column index number of two, the Vlookup function will return the value in the second column of row A1. The second column contains the value of the Product. The fourth parameter specifies whether you want an exact match or if you’d like to find the closest match. If you wish to choose the first value, you can select False; otherwise, determine the next smaller value.
The next time you use a lookup function, you should ensure the value is valid. If it’s not, you may get errors. For example, you’ll see an error if the value is not a number. You can avoid this by using a double-unary operator (–) or the TEXT function, which converts numbers to text.