How to use the VLOOKUP Function for Beginers Part 1

Hi friends,
In my daily routine works in Microsoft Excel I can't think Excel without VLOOKUP function and if you don't know how to use VLOOKUP in Excel then I think you will have to face lot of problems while preparing data. I can remember one of my colleagues who occasionally gave a statement that almost all the Excel generated reports are dependent upon VLOOKUP, and sometimes few Companies also dependent upon VLOOKUP!!!
That not true, he was kidding... There are various ways to match column wise data in Excel, for example you can use INDEX and two MATCH function in nested condition in place of VLOOKUP. You can check this in this link .

VLOOKUP in Microsoft Excel is an indispensable function and today in this article we'll discuss about the very basic VLOOKUP function, step-by-step. Vlookup looks for a value Column wise, therefore you can get column wise matching data using this function.

Free Download Demo Excel File : Microsoft Excel Vlookup Function File

Description

The VLOOKUP function performs a vertical lookup by searching for a value in the first column of a table and returning the value in the same row in the index_number position.

The VLOOKUP function is a built-in function in Excel that is categorized as a Lookup/Reference Function. It can be used as a worksheet function (WS) in Excel. As a worksheet function, the VLOOKUP function can be entered as part of a formula in a cell of a worksheet.

The VLOOKUP function is actually quite easy to use once you understand how it works! If you want to follow along with this tutorial, download the example spreadsheet. 

Watch the Youtube video here How to use VLOOKUP

 In simple language, this function is used to find out the data from large database providing 4 parameters.

What need to do ? As shown in the below picture cell number 'W2' write "=vlookup(lookup value, table array, column index number, [range lookup]) for exact match always put '0' (zero to [range lookup]". 

Follow the below syntax. Practice few times download my working files, watch my video, and you will feel it this function as very easy and very useful function.

Basic VLOOKUP function

Syntax

The syntax for the VLOOKUP function in Microsoft Excel is
VLOOKUP( value, table, index_number, [range lookup] )

Parts of VLOOKUP Function in Excel

Parameters or Arguments

value
The value to search for in the first column of the table.

table
Two or more columns of data that is sorted in ascending order.

index_number
The column number in table from which the matching value must be returned. The first column is 1.

approximate_match
Optional. Enter FALSE to find an exact match. Enter TRUE to find an approximate match. If this parameter is omitted, TRUE is the default.

This process to use VLOOKUP is the very basic and very easy method but you need to know the advanced VLOOKUP also, click here to move to the Part 2 of VLOOUP function.

How to use the COUNTIF Function

Microsoft Excel COUNTIF function one of the most important functions because of its property to give the count number using the given criteria.

In this article we'll discussion about COUNTIF step-by-step. Its very easy quick working formula...

Description

COUNTIF is a function in Excel to count cells that meet a single criteria. COUNTIF can be used to count cells with dates, numbers, and text that match specific criteria. The COUNTIF function supports logical operators (>,<,<>,=) and wildcards (*,?) for partial matching.

Excel File Download Link : COUNTIF Tutorial Excel

But, remember you need to put logical operators within double quote. Non-numeric criteria needs to be enclosed in double quotes but numeric criteria does not require quotes.

Syntax

The syntax for the COUNTIF function in Microsoft Excel is:

COUNTIF (range, criteria)

Arguments

range - The range of cells to count.

criteria - The criteria that controls which cells should be counted.

The COUNTIF function in Excel counts the number of cells in a range that match the supplied criteria

    COUNTIF(A1:A10,100)          // count cells equal to 100

    COUNTIF(A1:A10,">32")      // count cells greater than 32

    COUNTIF(A1:A10,"Tim")      // count cells equal to "Tim"

    COUNTIF(A1:A10,"<"&B1)  // count cells less than value in B1

Follow the bellow image. For more clarification about this function you can watch my YouTube video by clicking Countif.

Countif Function in Excel

    

       OR  you can watch here....

How to use the TRANSPOSE Function using Formula and with mouse clicks

Today's article is about TRANSPOSE function in both ways - formula & Paste Special step-by-step.

Description

The Microsoft Excel TRANSPOSE function returns a transposed range of cells. For example, a horizontal range of cells is returned if a vertical range is entered as a parameter. Or a vertical range of cells is returned if a horizontal range of cells is entered as a parameter.

The easiest way to use TRANSPOSE is to copy the data, then right click on the cell you want to transpose the data. Click on paste special, select Transpose, click OK. That's it, you are done. (Reference below image).

  

Transpose by Paste Special

Using TRANSPOSE as function is a tricky thing. Hope you know about array (I'll discuss about array after few post from now).

For now, an Array is a set of numbers.

Syntax

The syntax for the TRANSPOSE function in Microsoft Excel is:

Transpose (range)

Parameters or Arguments

range

The range of cells that you want to transpose.

The range value in the TRANSPOSE function must be entered as an array. To enter an array, enter the value and then press Ctrl+Shift+Enter. This will place {} brackets around the formula, indicating that it is an array.

Please don't forget to select all the blank cells you want to put the transposed value, please follow the below image.

If you have any question or query please let me know through comment box.

Transpose using function and Array

How to use the SUBTOTAL Function to Sum, Average, Count, Maximum, Minimum, Product

This Excel tutorial explains how to use the Excel SUBTOTAL function step-by-step with syntax and examples.

Where to use ? Its a Math/Trig function, therefore, the use is as per your choice in calculations. You can easily calculate a lot of things using this function. Below is the few list of the value and their explanations.

Description

The Microsoft Excel SUBTOTAL function returns the subtotal of the numbers in a column in a list or database.

Syntax

The syntax for the SUBTOTAL function in Microsoft Excel is:

SUBTOTAL( function num, range1, [range2, ... range_n] )

function num

It is the number of the function listed within the function SUBTOTAL like, for AVERAGE number is '1', alternatively you can use comma. In simplier language it indicates the function used. Number '9' means SUM in this fuction as far as I can remember.

range1, range2, ... range_n

The ranges of cells that you want to subtotal.

The below image shows how this formula looks while applied sum, as you know '9' means the function SUM.

SUMIFS Function Step by Step

This Excel tutorial explains how to use the Excel SUMIFS function step-by-step with syntax and examples that works very quickly.

I'll again discuss about this Fumction latter in this blog in detail, for now, please follow this blog

Tutorial Excel Download Link : SUMIFS Sample Tutorial

Description

The Microsoft Excel SUMIFS function adds all numbers in a range of cells, based on a single or multiple criteria.

The SUMIFS function is a built-in function in Excel that is categorized as a Math/Trig Function. It can be used as a worksheet function (WS) in Excel. As a worksheet function, the SUMIFS function can be entered as part of a formula in a cell of a worksheet.

Syntax

The syntax for the SUMIFS function in Microsoft Excel is:

SUMIFS( sum_range, criteria_range1, criteria1, [criteria_range2, criteria2, ... criteria_range_n, criteria_n] )

Parameters or Arguments

sum_range

The cells to sum.

criteria_range1

The range of cells that you want to apply criteria1 against.

criteria1

It is used to determine which cells to add. criteria1 is applied against criteria_range1.

criteria_range2, ... criteria_range_n

Optional. It is the range of cells that you want to apply criteria2, ... criteria_n against. There can be up to 127 ranges.

criteria2, ... criteria_n

Optional. It is used to determine which cells to add. criteria2 is applied against criteria_range2, criteria3 is applied against criteria_range3, and so on. There can be up to 127 criteria.

Example (as Worksheet Function)

Let's look at some Excel SUMIFS function examples and explore how to use the SUMIFS function as a worksheet function in Microsoft Excel:

Watch video in this link SUMIFS

or you can watch here