What is Vlookup in Excel

Vlookup (Vertical Lookup) is a built-in Excel function that allows us to search for a specific value in a table array, and return a corresponding value from a specified column.

It is commonly used to search data from large spreadsheets or tables.

The basic syntax of the Vlookup function is as follows:

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

lookup_value - The value you want to look up. This can be a number, text, or a cell reference.

table_array - The range of cells that contains the data you want to search. This can be a single sheet or a multi-sheet references.

col_index_num - The column number in the table_array from which the matching value should be returned.

This is the column in the table_array that contains the value that you want to return.

range_lookup -  A logical value (TRUE or FALSE) that specifies whether the function should find an exact match (FALSE) or an approximate match (TRUE).

Let us understand this through an example

For easy understanding, I have created this dummy table, having  four fields, Employee Id, Grade, First name and last name

He we will try to fine the First name or Last name of an employee based on  the Employee Id

Here you can see, we are searching for First name and last name of employee whose employee id is 3, 4 and 5.

It is clear from the formula, highlighted in red bold text

H4 is the lookup value i.e. the employee id

B3:E16 is the table array, this is the base table as shown in the table Data1

3 is the column index number.

The first column of table array would be the column index number1, 2nd column would be the column index number2 and so on.

0 is the match type, if we want to exact match the number or text we put 0 on the other hand for approximate match we put 1

Advantage of Vlookup Formula

1. It allows you to quickly search for and retrieve data from large spreadsheets or tables, saving you time and effort.

2. It ensures that data is returned based on a specific value, reducing the risk of errors.

3. It can be used to look up data from multiple worksheets within the same workbook, and it can also be used in combination with other formula or function.

4.It has a simple syntax and is easy to understand, making it accessible to users of all skill levels.

5.  It can be used to create dynamic data, where the value returned by the function updates automatically when the data in the table changes.

Conclusion:

VLOOKUP is a very powerful and versatile tool that can save you time and increase the accuracy, making it an essential function.

For anyone who works with large spreadsheets or tables in Excel or in google sheet.

If you thing information give is valuable, hit like like button and share as much you can. Please feel free the give comments.

Thanks.

Row Function in M S Excel

Dear friends today I will discuss what is row function in excel, I will discuss in detail about it with 2-3 example, so be with us and read full blog

What is Row Function in Excel

Row function of excel provide unique sequence to each row number excel database or within the result data set.

See the below example, you can see each row has unique sequence number.

How Row function works in excel

Row function is a built in function of excel. It takes only one argument i.e. cell reference and give the row number.

=Row(A5) it will return 5

= Row(A6) it will return 6

=Row(10) it will return 10

Note here row function return the row number not the value of the cell

Example1

Here you can see the parameter/argument passed in the row function and its corresponding result

Let us understand with the help of one more example

Suppose you have to generate a serial number starting from row number 4 i.e. C4. From C4 for C10

the row function =ROW(C4) will give you result 4, but you want 1 at Cell C4. In such case your have to subtract 3 from that value like 

=ROW(C4) - 3, this will return 1 and if you drag down it will create a sequence form C4 to C10

Example2

    =ROW(A1:B10) - What it will return?

It will return 10 ,this means there is 10 rows available in the selected range.

Example3

If we want to count the number of available rows in the range A1:A1000, we can put the formula like =ROW(A1:A200)

In this case it will return 200, that means there is 200 rows available in the selected range or the count of available rows is 200 in the selected range 

 

Let us understand with some more example

Suppose you have list of student with name and roll number. As you know the roll number is both odd and even,

Now the task is to highlight the all the student having even roll number

Example4

This is the list of name of students with roll number at the end, 

Now the task is to highlight the Odd row number with green color

There are various way to achieve this task, I am showing my own way you pleas show your way in comments

Step1. Go to Conditional formatting in home section and then choose New rule

Step2. Once you click on the new rule, a new dialogue box will appear. From that dialogue box choose the last option Use formula to do formatting and put the formula below the box highlighted

Step3. In the below text box put formula =EVEN(ROW())=ROW()

            

Step4. Select the desired color you want and select OK

And you will get the result.

For more such amazing excel technique, please visit our You tube channel  Crazy Excel Tricks

Also visit

For more clarity pleas visit the below link