noobmint.blogg.se - How to use vlookup in excel to solve

#How to use vlookup in excel to solve download#

So in the given data, the formula converts the marks into a grade from the class table or “Key” table.

But make sure that the marks in the cell have to be in ascending order.

As per the above image, you can see that we have made a table of students with marks to identify their class we have made another table that will act as the key,.

To define class from the marks, we can use an approx match to define the class against the Marks.Īfter using the VLOOKUP formula, the result is shown below.

You can see from the above image, after getting the value in the first row, we can drag the same and get to throughout data against the given roll nos if it is available in the given table.

As you can see from the below image, we have successfully retired the entire data from Example #2 to Sheet # 3.

So as a column indicator, we have written 3, and then 0 means False as we want exact data match. From the above images, you can see that the range of the formula has been indicated with ‘ Example#2′ as the data we needed will be retrieved from Example #2 and column #3.Drag down the formula for the next cell.As you can see, the image on the right side is of another sheet, sheet number 3.We have a table in sheet number 2 as per the following image we will find the result of this student in sheet number 3 from their roll nos.Vlookup from a different sheet is very much similar to the Vlookup from the same sheet, so here we have changed in the ranges, as here we have different worksheets.It seems pretty useless as here we are dealing with small data, but it is very useful while dealing with large data.Įxample #3 – Vlookup from a Different Sheet.Suppose we have Numeric data instead of the name or word or alphabetical we can do this more precisely and with more authentic logic.In our example, we have written Mahendra in the first name, and still, we are getting the result correctly this can be a bit complicated while you’re playing with the words but more comfortable while you’re working with Numbers.After applying VLOOKUP Formula, the results are shown below.So even after you make some spelling mistake or a grammatical error in our case, you don’t need to worry, as it will find the most matched data and provides the result of mostly matched.This formula will provide the same results, but it will start to look from top to bottom and provides the value with the most approx match.If we apply the same formula with True or 1 instead of False or 0, we will not have to worry about providing the exact data to the system.Here we have used False OR 0, which means it is matching the absolute value, so by applying even extra space to our first name, it will show the #N/A means data not matching.Įxample #2 – Approximate Match (True or 1).Here you can see we have written 3 as we want the data of column #3 from the range we have selected.We can see that just from the first name, we have got the last name and the runs scored by that student If we retrieve runs from the last name column, it will be called chained Vlookup.After applying VLOOKUP Formula, the result is shown below.So let’s add the third column on the right side, “Runs”.Now we can also widen the data from a single data available, so by just filling the First Name, we will have the Last name of the person and the runs scored by that person.From the Image above, we can see that by writing the name Rahul in the column, we got Dravid the second name according to the table on the left.

When we enter the first name from a table, we are supposed to get the formula’s last name.

Here when we place data in E3, which is the first name from our given table, the formula will give us the Last Name.

We can select the table too instead of the entire row In the formula, you can see written 2 before False as a column indicator because we want data to be retrieved from column # 2 from the selected range.

We have made the table by using Vlookup, we will find the Last name of the person from their first name all the data has to be available in the table which we are providing the range to find the answer within (A:C in this case). In cell F2, we are using VLOOKUP Formula. Here for this example, let’s make a table to use this formula suppose we have the data of students as shown in the image below.

#How to use vlookup in excel to solve download#

You can download this VLOOKUP Examples Excel Template here – VLOOKUP Examples Excel Template Example #1 – Exact Match (False or 0)