If you are a user of Excel, you would have heard about the Excel Vlookup function in passing. Not many people are clear on what the function actually does so here are some things you should know about Excel Vlookup.

The function is actually used for looking up values that belong in the vertical array of data, and returning the corresponding one from another column in the same array. It is best used for exact matching and also closest matching to the lookup value.

For Excel Vlookup to work, there are certain factors that are required to be followed. First of all, the data on the supplied table_array has to be organised in columns. Upper and lower case texts are perceived as equal but numbers and text are not perceived as equal, and all characters inclusive of spaces are included when you are searching for matches.

If you are using the function and you want an exact match, then the lookup range argument should be set to false while the Vlookup function searches for the exact match. If there are no exact matches to be found, the function will reveal an #N/A which is an error. If the results are conclusive, then Vlookup will use the first match that it finds. However, if the lookup value is a string of text, you can use characters that are considered wildcard- ? matches with any single character and * matches with any set characters.

If you are looking for the closest match, then the range look up argument should actually be set to true so that it can match to the closest value, depending on if it is below or equal to the value. The supplied array table should also be done in ascending order, otherwise, it may lead to unpredictable results. If the look up value is smaller than all of the supplied array table, then the #N/An error would be seen.

These are the basic rules needed to operate the Vlookup function without any errors or inaccurate results. It may seem confusing or complicated at first but the function is surprisingly easy to use. Once you have familiarised yourself with it, it would become like second nature to you.