Why vlookup

Absolute ranges are pretty ugly looking, so can make your VLOOKUP formulas a lot cleaner and easier to read by replacing absolute references with named ranges, which are automatically absolute. For example, in the employee data example above, you can name the input cell "id" and then name the data in the table "data", you can write your formula as follows:. Not only is this formula easier to read, but it's also more portable, since named ranges are automatically absolute.

This is because hard-coded column index values don't change automatically when columns are inserted or deleted. In this example, the lookups for Rank and Sales were broken when a new column was inserted between Year and Rank. Year continues to work because it is on the left of the inserted column:. If you're getting data from consecutive columns, this trick lets you set up one VLOOKUP formula, then copy it across with no changes required.

For the first formula in cell C3, COLUMN by itself will return 3 because column C is third in the worksheet so we simply need to subtract one, and copy the formula across:. Taking the above tip one step further, you can use MATCH to look up the position of a column in a table and return a fully dynamic column index.

An example would be looking up sales for a salesperson in a particular month, or looking up the price for a particular product from a particular supplier. It may seem counterintuitive, but wildcards let you do an exact match based on a partial match :. If you like, you can adjust the VLOOKUP formula to use a built-in wildcard, like the example below, where we simply concatenate the value in H3 with an asterisk. They give you an easy way to create a "lazy match", but they also make it easy to find the wrong match.

In one way, this is useful because it tell you definitively that there is no match in the lookup table. To trap this error and display a "not found" message instead of the error, you can simply wrap the orignal formula inside of IFERROR and set the result you want:.

Here is the formula:. If you are simply retrieving numbers as text from a column in a table, it doesn't matter. In the following example, the ids for the planet table are numbers entered as text , which causes VLOOKUP to return an error since the lookup value is the number To solve this problem, you need to make sure the lookup value and the first column of the table are both the same data type either both numbers or both text.

One way to do this is to convert the values in the lookup column to numbers. An easy way to do this is to add zero using paste special. If you don't have easy control over the source table, you can also adjust the VLOOKUP formula to convert the lookup value to text by concatenating "" to the value like so:.

If you've ever built a series of nested IFs, you know that they work fine, but they require a bit of parentheses wrangling. Easy to follow. No jargon. Pictures helped. Didn't match my screen. Incorrect instructions. Too technical. Not enough information. Not enough pictures. Any additional feedback? Submit feedback. Thank you for your feedback! In most cases people are looking for a particular product, order, employee or customer and therefore require an exact match.

This argument is optional, but if left empty, the TRUE value is used. The TRUE value relies on your data being sorted in ascending order to work. Click on the references within the formula and press the F4 key on the keyboard to change the reference from relative to absolute. Because this is entered as an index number, it is not very durable. The image below shows such a scenario.

The quantity was in column 3, but after a new column was inserted it became column 4. Get instant live expert help with Excel or Google Sheets. Post your problem and you'll get expert help in seconds Your message must be at least 40 characters.

Our professional experts are available now. Your privacy is guaranteed. Connect to an Expert. Extra Spaces in Lookup Value. In big data set it is very hard to identify these leading or trailing spaces in lookup values that cause the VLOOKUP function to not find the match and return NA error.

Numeric values are formatted as Text. So you need to follow its following syntax to provide it fully.