Trending December 2023 # How To Use Vlookup In Excel # Suggested January 2024 # Top 20 Popular

You are reading the article How To Use Vlookup In Excel updated in December 2023 on the website Kientrucdochoi.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested January 2024 How To Use Vlookup In Excel

The VLOOKUP function in Excel scares a lot of people because it has a lot of parameters and there are multiple ways to use it. In this article you’ll learn all of the ways you can use VLOOKUP in Excel and why the function is so powerful.

Table of Contents

VLOOKUP Parameters In Excel

When you start typing =VLOOKUP( into any cell in Excel, you’ll see a pop-up showing all of the available function parameters.

Let’s examine each of these parameters and what they mean.

These four parameters let you do a lot of different, useful searches for data inside of very large datasets.

A Simple VLOOKUP Excel Example

VLOOKUP isn’t one of the basic Excel functions you might have learned, so let’s look at a simple example to get started.

It would be very time consuming to search through such a large dataset to find the school that you’re interested in.

Instead, you can create a simple form in the blank cells on the side of the table. To conduct this search, just make one field for School, and three additional fields for reading, math, and writing scores.

Next, you’ll need to use the VLOOKUP function in Excel to make these three fields work. In the Reading field, create the VLOOKUP function as follows:

Type =VLOOKUP(

Select the School field, which in this example is I2. Type a comma.

Select the entire range of cells that contain the data you want to look up. Type a comma.

When you select the range, you can start from the column you’re using to look up (in this case the school name column), and then select all of the other columns and rows that contain the data.

Note: The VLOOKUP function in Excel can only search through cells to the right of the search column. In this example, the school name column needs to be to the left of the data you’re looking up.

Next, to retrieve the Reading score, you’ll need to select the 3rd column from the leftmost selected column. So, type a 3 and then type another comma.

Finally, type FALSE for an exact match, and close the function with a ).

Your final VLOOKUP function should look something like this:

When you first press Enter and finish the function, you’ll notice the Reading field will contain an #N/A.

This is because the School field is blank and there is nothing for the VLOOKUP function to find. However, if you enter the name of any high school you want to look up, you’ll see the correct results from that row for the Reading score.

You may notice that if you don’t type the name of the school in the same case as how it’s listed in the dataset, you will not see any results.

This is because the VLOOKUP function is case sensitive. This can be annoying, especially for a very large dataset where the column you’re searching through is inconsistent with how things are capitalized.

To get around this, you can force what you’re searching for to switch to lowercase before looking up the results. To do this, create a new column next to the column you’re searching. Type the function:

This will lowercase the school name and remove any extraneous characters (spaces) that might be on the left or right side of the name.

Now that all of your data is cleaned up in this new column, slightly modify your VLOOKUP function in Excel to use this new column instead of the previous one by starting the lookup range at C2 instead of B2.

Now you’ll notice that if you always type your search in lower case, you’ll always get a good search result.

This is a handy Excel tip to overcome the fact that VLOOKUP is case sensitive.

While the exact match LOOKUP example described in the first section of this article is pretty straightforward, the approximate match is a little more complex.

The approximate match is best used to search through number ranges. To do this correctly the search range needs to be properly sorted. The best example of this is a VLOOKUP function to search for a letter grade that corresponds to a number grade.

If a teacher has a long list of student homework grades from throughout the year with a final averaged column, it would be nice to have the letter grade corresponding to that final grade come up automatically.

This is possible with the VLOOKUP function. All that’s required is a lookup table off to the right that contains the appropriate letter grade for each numerical score range.

Now, using the VLOOKUP function and an approximate match, you can find the proper letter grade corresponding to the correct numeric range.

In this VLOOKUP function:

lookup_value: F2, the final averaged grade

table_array: I2:J8, The letter grade lookup range

index_column: 2, the second column in the lookup table

[range_lookup]: TRUE, approximate match

Once you finish the VLOOKUP function in G2 and press Enter, you can fill in the rest of the cells using the same approach described in the last section. You’ll see all of the letter grades properly filled in.

Note that the VLOOKUP function in Excel searches from the bottom end of the grade range with the assigned letter score to the top of the range of the next letter score.

So, “C” needs to be the letter assigned to the lower range (75), and B is assigned to the bottom (minimum) of its own letter range. VLOOKUP will “find” the result for 60 (D) as the closest approximate value for anything between 60 to 75.

VLOOKUP in Excel is a very powerful function that has been available for a long time. It is also useful for finding matching values anywhere in an Excel workbook.

Keep in mind, however, that Microsoft users who have a monthly Office 365 subscription now have access to a newer XLOOKUP function. This function has more parameters and additional flexibility. Users with a semi-annual subscription will need to wait for the update to roll out in July 2023.

You're reading How To Use Vlookup In Excel

Excel Vlookup – Sorted List Explained

In my previous Excel VLOOKUP formula tutorial I mentioned that there are two ways you can use a VLOOKUP but most people know one way or the other, and only a few know both.

As promised here’s the second way to use it, and I call it the Sorted List version as it relies on the data in the table you are referencing being sorted.

Note: If you haven’t read the first tutorial, then I recommend you first watch the video below from the beginning to get an understanding of how VLOOKUP works.

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.

Please enter a valid email address.

First let’s set the scene:

In the image below we want to lookup the Commission Rates table in cells G6:I13, and find the rate in column I based on the sales values in column D, and return the result to column E.

Excel VLOOKUP Function syntax:

lookup_value

table_array

col_index_num,

range_lookup

And to translate it into English it would read:

find this value

in that table

return the value in the nth column of the table

find an exact match if you can, but if not, find the next lowest match

Note: with the Sorted List version we want Excel to find the next closest option in our table, i.e. an approximate match. To specify this we can leave the ‘range_lookup’ argument blank, or enter TRUE, or 1.

Remember we want Excel to find the Commission % Rate and enter it in cell E6, so in English our formula will read:

find where the Sales amount $3,112

falls in the Commission Rates table G6:I13

return the value in column 3 of the table

if there isn't an exact match, find the next closest value

$G$6:$I$13

TRUE

Let me clarify some points:

1) ‘find where Sales amount $3,112, falls in the Commission Rates table’ – Excel doesn’t actually take into consideration column H in our table. I have simply put it there to help understand the commission ranges. Excel is in fact looking for the exact amount $3,112 in our Commission Rates table, and when it can’t find it, it finds the next best lower amount and returns the value in column 3.

3) If we had consecutive duplicates in our Commission Rates table Excel will find the last instance of the value and return the result in column 3. For example, if instead of the amount $4001 in cell G11, you had $3001 again. Excel would return the value of 6% as it’s finding the last best match for our amount. The tip here is to remove any duplicates or you’ll end up with erroneous results.

4) Unlike the VLOOKUP Exact Match version of the formula, this version requires the list to be sorted in ascending order. Just like with duplicates explained above, if it’s not sorted you will end up with erroneous results.

You’ll notice in the formula bar above there are ‘$’ signs around the reference to the table. This is called an absolute reference and it allows us to copy the formula down column E without Excel dynamically updating the table range as we copy.

Check out my previous tutorial for VLOOKUP Rules & Common Mistakes!

How To Use Len Function In Excel?

LEN in Excel (Table of Contents)

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

LEN function in Excel, where Len is taken from a length, is used to count the number of characters in a cell. LEN function considers special characters, spaces, and alphabets and counts all these values if a selected cell exists. The most important thing to know about the LEN function is it only counts the character length for only one cell. If we select any range of cells, we will get the #Value error.

LEN Formula in Excel:

Below is the LEN Formula.

LEN Function consists of only one parameter, i.e., TEXT

TEXT: The text you want to find the length of the characters.

Note: Space is also considered as one character.

This LEN Function is very simple and easy to use. Let us now see how to use the LEN function in Excel with the help of some examples.

You can download this LEN function Excel Template here – LEN function Excel Template

I got 13 on the result.

In the below example, I have used the LEN function to count letters in a cell. Here I have taken a date as an example.

Even though the above date has 10 characters, I got only 5. The reason behind it is that LEN Function counts the characters in the value of a cell and is not concerned with formatting.

Now I will explain the huge benefit of the LEN function when you want to compare two similar sets of data sets.

LEN Function with TRIM is a beautiful combination in Excel. We have two similar values in cells A1 and B1, and we need to compare whether both are the same.

The values in Cell A1 & B1 look similar, but comparing them in Cell C1 shows me FALSE. Looking at the outset looks similar, but not able to recognize the difference here.

The LEN function can save me here. Apply LEN to A1 and determine how many characters are in the A1 cell.

So in cell A1, 14 characters are there.

Apply LEN to B1 to find out how many characters are there in the B1 cell, and in cell B1, 13 characters are there

, which is why the formula shows it as two different values.

Because of one extra trailing space after the word Excel Formula, it is showing as 14 characters. We can remove unwanted spaces by using the TRIM function.

Therefore, TRIM has removed the unwanted space and returned only the text value; now, our formula in cell C1 shows both values as TRUE.

Below are the names of the employees, and you needed to extract the Last Name separately.

In the above example, we cannot just apply RIGHT with specified numbers because the last name of every employee is different.

For the first employee, the last 4 characters are 5, but for the second one,e it is 11 (including space, i.e., Robert Adam), and for the third one, it is 12 (including space, i.e., J. Johnston)

Here we need to use FIND & LEN function to determine the number of characters from the given text.

and the answer will be:

Similarly, We Find the other two

Part 1: This part determines the desired text that you want to extract from the characters.

Part 2: LEN function will give you the total number of characters in the list. We will see the detailed article on LEN in the upcoming articles.

The answer is:

Part 3: The FIND function determines from which number space begins, i.e., The End of the first name. We will see the detailed article on FIND in the upcoming articles.

The answer is:

Len gives 16 characters and Finds 5 for the first employee. That means LEN(E3) – FIND(F3) (16 – 5) = 11 characters from the right side

the result will be Robert Adam

Len gives 17 characters and Find 5 for the first employee. That means LEN(E4) – FIND(F4) (17 – 5) = 12 character from the right side

the result will be J. Johnston

We can also use the MID function in VBA code, like Excel. The below code illustrates the usage of the LEN function in VBA macros.

If you run the above code message box will display 25 as your result.

LEN Function is useful when you count the number of characters in some text.

Formatting of numbers is not a part of the LEN function, i.e., if your value shows $1500.00, then it will show as 4 only instead of 8.

Numbers and dates will also return a length.

If a cell is blank,

it will return 0 in the result.

A cell with an error value will return that error in the result.

You can refer to only one cell at a time.

Otherwise, it will show an error as #Value.

This has been a guide to LEN Function. Here we discuss the LEN Formula and how to use the LEN function in Excel, along with practical examples and downloadable Excel templates. You can also go through our other suggested articles –

How To Use Vba Like Function In Excel?

Introduction to VBA Like

VBA Like is used when we have some special characters, spaces in the string and we need to get exact or most relevant output from that word. VBA Like allows us to match the pattern in alphabetical sequence so that if any word contains some special characters then with the help of VBA Like we can complete word. We can also determine if that string is in the proper format or not.

In VBA Like, we have some conditions on that basis we can define what we need to get and how we need to fill the space of missing blank words.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Question Mark (?) – By this, we can match only one character from the string. Suppose we have string “TAT” and the pattern is “T?T” then VBA Like will return TRUE. If we have the string as “TOILET” and the pattern is still “T?T” then VBA Like will return FALSE.

Asterisk (*) – By this, we can match 0 or more characters. Suppose we have the string as “L**K” then VBA Like will return TRUE.

[Char-Char] – By this, we can match any single character in the range Char-Char.

[!Char] – By this, we can match any single character but not in the list.

[!Char-Char] – By this, we can match any single character but not in Char-Char.

We will learn how to use a VBA Like function with a few examples in excel.

You can download this VBA Like Excel Template here – VBA Like Excel Template

To find if the available string is TRUE or FALSE for VBA Like first, we need a module. For that,

Step 2: Now in the opened window of Module in VBA, write the subcategory of VBA Like as shown below.

Code:

Sub

End Sub

Step 3: Now first, we will define a variable A as String as shown below. Here, we can use the Long variable as well as it too allows to store any text value in it.

Code:

Sub

Dim

As String

End Sub

Step 4: Next, we will assign a word to variable A. Let’s consider that word as “LIKE”.

Code:

Sub

End Sub

Step 5: Now with the help of If-End If loop we will create VBA Like condition.

Code:

Sub

Dim

As String

End If

End Sub

We will use the above code in the upcoming example as well directly.

Step 6: Now in If-End If loop write the condition as variable A like “L?KE” is a TRUE condition then give us Yes in a message box or else give us No in the message box for FALSE.

Code:

Sub

Dim

As String

Then

Else

End If

End Sub

We have kept a question mark in the second position. But this can be kept anywhere in whole string.

We will get the message box as NO. Which means, the word which chose “LIKE” in variable A may have other alphabets in place of a question mark and instead of only “I”.

In this example, we will implement Asterisk (*)

Step 1: Now we will use the same code structure which we have seen in example-1 with the same word “LIKE”.

Code:

Sub

Dim

As String

End If

End Sub

Step 2: As we know that with Asterisk we have a match 0 or more characters from any string. So in If-End If loop we will write, if VBA Like matches “*Like*” is TRUE then we will get the message as Yes, else we will get No if it is FALSE.

Code:

Sub

Dim

As String

Then

Else

End If

End Sub

Step 3: Again compile the complete code and run it. We will get the message as NO because VBA Like is failed to match any alphabet apart from defined string “Like”.

Step 4: Now if we change the string A from “Like” to “Like Wise” and try to match any letter from the string, let’s say it is “W” in asterisk then what will we get?

As said above, we have used “LIKE WISE” as our new string.

Code:

Sub

Dim

As String

Then

Else

End If

End Sub

Step 5: Now compile the code and run it again. We will get the message as YES. Which means that VBA Like is able to match any alphabet from our string “LIKE WISE”.

In the same manner, if we match any other letter from “LIKE WISE” we may get the same results.

In this example, we will see, how Char-Char works in matching the strings of characters.

Step 1: For this also, we will use the same frame of code which we have seen in example-2 for defined variable A as “LIKE WISE”.

Code:

Sub

Dim

As String

End If

End Sub

Step 2: In if-End If loop, write the condition VBA Like matches letters from I to K (In Asterisk and Char) then it will be TRUE and give us the message as YES. If not then it will be FALSE and we will get the message as NO.

Code:

Sub

Then

Else

End If

End Sub

Step 3: Again compile the code and run it. We will see, VBA Like is able to match the characters from letter I to K and gave us the message as YES.

In a set of database where it is quite frequent to see such special characters, there using VBA Like will allow us to frame hidden words.

As it has very limited application, so it is very rarely used.

We can compare and match only strings. Any other variables such as integers, double cannot be used.

It is not recommended to record a macro on VBA Like. As we don’t know any excel function on it. And also, doing this process with other ways may result in getting incorrect match results.

Though VBA Like is very rarely used, but the kind of output it gives may not be accurately given by other functions and command of the same type.

Save the file in Macro Enable Excel file format only. This format is mainly used when we create any macro.

This is a guide to VBA Like. Here we discuss how to use Excel VBA Like function along with practical examples and downloadable excel template. You can also go through our other suggested articles –

How To Use Object In Excel Vba With Examples?

VBA Object

In Microsoft Excel, a VBA Object can contain one or more than one object. Such as a single workbook can have one or more than one worksheet. Workbook, Worksheet, Range, and cells are the objects in Excel. Each object has its own properties. And they all have a different method of application. Let say the workbook is the main object which has 2 worksheets in it. Those 2 worksheets will be its child object. One of the sheets has one range, so that sheet will become the main object, and the range will be its child object.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Let’s see the examples of object in Excel VBA.

You can download this VBA Object Excel Template here – VBA Object Excel Template

It can be written in many ways. Suppose we need to print any text in a cell, so this can be done in various ways. We can directly use the range value to that cell. This direct method is not a part of VBA Object but a process of performing a task that could be done by VBA Object as well. For this:

Step 1: Go to VBA and insert a Module from the Insert menu option as shown below.

Step 2: Now write the Sub Category of performed function, like VBA Object, in any other name as per your choice, as shown below.

Sub

End Sub

Step 3: Now select the range function considering any cell, let’s say cell B3 with Value as shown below.

Code:

Sub

End Sub

Step 4: Now add text or word in that range cell as shown below.

Code:

Sub

End Sub

Step 5: Now, compile the code and run it by pressing the play button located below the menu bar.

This is the normal way of printing text to any cell. How we will see how the same process can be done when we use VBA Object. For this, we will need another fresh module. And in that,

Step 1: Go to VBA and insert a Module from the Insert menu option as shown below.

Step 2: Write the Sub Category of VBA Object as shown below.

Code:

Sub

End Sub

Step 3: Here, we will see the complete VBA Object from the main to a child category. First, select the currently opened workbook with the help of the command ThisWorkBook with Application, as shown below. This will select the workbook which is actually opened and last selected.

Code:

Sub

End Sub

Step 4: Now select the sheet which is currently opened in the workbook, or we can write the name of the worksheet as well. Here, we have written the name of sheet Sheet1 in inverted commas, as shown below.

Code:

Sub

End Sub

Step 5: Now comes the range. Select the range of the cell where we need to print or insert the text. Here we are selecting cell B4, as shown below.

Code:

Sub

End Sub

Code:

Sub

End Sub

Step 7: Now, compile and run the code. We will see cell B4 has the text “VBA Object”.

This complete process is called VBA Object. In which we have first selected the main object, i.e. Workbook, which has its child object, i.e. Sheet1, and that has another child object range, i.e. cell B4.

There are many different ways to add text to any cell. One can be with Excel VBA Object, and others can be without it. In these categories, again, there are many ways to do it. Let’s see another way to add VBA Object. For this, we will need a module.

Step 1: In that module, add a subcategory; better make it with sequence number as shown below.

Code:

Sub

End Sub

Step 2: Select the worksheet by adding the name of the current worksheet, which is Sheet1, as shown below.

Code:

Sub

End Sub

Step 3: Now, add the range of the cell where we want to see the output, as shown below.

Code:

Sub

End Sub

Step 4: At last, give it a value that we can see once we run the code. We are considering the same text as seen in example 1.

Sub

End Sub

Step 5: Now run the code. We will see; cell A3 got the text which we wanted to add there, as shown below.

In this process, we have directly added the worksheet. So Worksheet will become our Object, and Range will become its child object.

Step 6: There is another way to perform the same task. In the bracket of the worksheet, instead of writing the sheet name, we can write the sequence of the sheet, which is 1, as shown below.

Code:

Sub

Worksheets("Sheet1").Range("A3").Value = "VBA Object"

End Sub

Step 7: Now run the modified code. We will see cell B3 got the same text VBA Object as cell A3, as shown below.

By keeping both the code in line, we can see and compare the changes we made. In another way,

Step 8: Instead of the Worksheet function, if we use the Sheet with sequence and selecting cell C3 as range as shown below.

Code:

Sub

'Worksheets("Sheet1").Range("A3").Value = "VBA Object"

'Worksheets(1).Range("B3").Value = "VBA Object"

End Sub

Step 9: Now run this code. We will see, again the same text will get added in range cell C3.

In all the methods which we have seen in this example, Worksheet is our object, and the range of the cell is child object or Sub-object.

We can make as many objects and link them together to sink them.

It makes use of Workbook, Sheet, and Range easy.

This allows a user to make changes in a specific Workbook, Worksheet or Range.

The same process can be performed by a much shorter code with having the same result.

Worksheet and Sheet both have the same use.

We can select any worksheet of the same workbook of any number sequence.

While writing big lines of code, it is important to form an Object in which the Workbook, Worksheets, and Ranges of different cells are linked.

Must save the file in Macro-Enabled Excel format. It allows us to use the code and change the code whenever required.

This is a guide to VBA Object. Here we discuss how to use Object in Excel VBA along with practical examples and downloadable excel template. You can also go through our other suggested articles –

How To Insert Multiple Rows In Excel

Inserting rows in Excel is one of the most basic stuff that we do almost everyday. And there are many ways to do this.

Now there could be various situations where you need to insert rows in Excel. For example, you may want to insert a single row, or multiple rows that are all together, or multiple rows that are not together.

In this tutorial, you’ll learn four different ways to do insert multiple rows in Excel.

Using Keyboard Shortcuts.

Using Insert Options.

Using Mouse + Keyboard (super trick).

Using Insert Copied Cells Option.

You can insert multiple rows in Excel using more than one keyboard shortcut.

Here is the first one:

Select the cell above which you want to insert multiple rows in Excel.

Press Shift + Space-bar to select the entire row.

Once the entire row is selected, press Control and hit the plus key (+) from the numeric keypad.

If you don’t have the numeric keypad, press Control and Shift and plus key (+) on the regular keyboard.

This will insert a single row right above the row that you selected.

Once you have inserted a row, you can repeat the same action by using the F4 key.

To do this, just select the row above which you want to insert a row and press F4. Hitting the F4 just repeats the last action.

What if you want to insert multiple rows in Excel at one go?

In the above example, we saw how to insert one row using the keyboard shortcut. If you want to insert multiple rows at one go, you can easily do it with a slight twist.

Here is how to do it:

It does not matter if the rows have data or are empty. This technique works in all cases.

Use the keyboard shortcut Control and Plus key (or Control and Shift and Plus key if you have a regular keyboard).

This will automatically enter the same number of rows as you selected to begin with. So if you selected four rows, it will insert four rows above it (as shown below):

Here is another keyboard shortcut that you can use:

Hold the ALT key and Press I followed by R.

You can also use the Insert functionality in Excel to insert multiple rows in Excel.

Here are the steps to use this:

Select the rows above which you want to insert the rows. For example, if you want to insert 4 rows between Row #3 and #4, select four rows staring from Row #4.

This will enter four rows above between Row #3 and #4 (as shown below):

You can also use the Insert Option available in the Home chúng tôi use it:

Select the rows above which you want to insert the rows.

This one is a super trick.

If you want to insert multiple rows above or below a row, here are the steps:

Select a row above or below which you want to insert rows.

You will notice a small green square at the end bottom right of the row number.

Hover your mouse on that green square and press Control + Shift on your keyboard. You will notice that the mouse icon changes from a plus to a double line icon with arrow on both sides.

This is one of the best ways to quickly insert any number of rows.

Another quick way to insert multiple rows in Excel is to copy a blank row and paste in where you want. Since you are copying a blank row, it would be the same as inserting a new row.

Here is how to do this:

Select a blank row and copy it.

Select the rows above which you want to insert the new rows. Select as many rows as you want to insert.

This will insert rows above the rows that you selected (as shown below):

These are four ways you can use to quickly insert multiple rows in Excel.

Update the detailed information about How To Use Vlookup In Excel on the Kientrucdochoi.com website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!