Simple Excel VLookup() function

There are many functions that can help you to clean up your data and format it in a way that is easy to understand and use.

in this tutorial, we look at excel functions such as VLOOKUP(), MID(), RIGHT(), and other excel tricks to help you clean up and format data.

This tutorial is suitable for beginners and those who want to refresh their skills. you will be able to learn other insights apart from the aforementioned functions.

You can also download the excel sheet to make your practice easy.

Data cleaning is a very important part of data analysis, having great data cleaning and data formatting skills will help you to stand out and make your work and other people’s work very easy.

Watch the Tutorial on Youtube.

THE VLOOKUP FUNCTION

The VLOOKUP function in Excel is a function that allows you to search for a specific value in a range of cells, and then return a value from a different column in the same row.

It is commonly used to look up a value in a table of data based on a unique identifier in the first column of the table. The syntax for the VLOOKUP function is as follows:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

The lookup_value argument is the value that you want to look up in the table. The table_array argument is the range of cells that contains the table of data.

The col_index_num argument is the column number in the table from which the value should be returned.

The optional range_lookup argument is a logical value that specifies whether you want the function to perform an exact match or an approximate match.

If you set this argument to TRUE or omit it, the function will perform an approximate match. If you set it to FALSE, the function will perform an exact match.

For example, suppose you have a table of data that contains employee names and ID numbers, and you want to look up an employee ID based on the employee name.

The table might look something like this:

  A       B
1 Name    ID
2 John    123
3 Mary    456
4 Steve   789

In this case, you could use the following formula to look up the ID for the employee named “Mary”:

=VLOOKUP("Mary", A2:B4, 2, FALSE)

The result of this formula would be 456.

THE MID FUNCTION

The MID function in Excel is a function that allows you to extract a specific number of characters from a text string, starting at a specified position. The syntax for the MID function is as follows:

=MID(text, start_num, num_chars)

The text argument is the text string from which you want to extract the characters. The start_num argument is the position of the first character that you want to extract.

The num_chars argument is the number of characters that you want to extract.

For example, if you have the text string “Hello, world!” and you want to extract the characters “llo, w”, you could use the following formula:

=MID("Hello, world!", 3, 7)

The result of this formula would be “llo, w”.

Keep in mind that the position of the first character in a text string is always 1, not 0. So in the example above, the character “H” is at position 1, “e” is at position 2, and so on.

THE RIGHT FUNCTION

The RIGHT function in Excel is a function that allows you to extract a specific number of characters from the end of a text string. The syntax for the RIGHT function is as follows:

=RIGHT(text, num_chars)

The text argument is the text string from which you want to extract the characters. The num_chars argument is the number of characters that you want to extract.

For example, if you have the text string “Hello, world!” and you want to extract the last 5 characters, you could use the following formula:

=RIGHT("Hello, world", 5)

The result of this formula would be “world”.