VLOOKUP &BASIC EXCEL FORMULAS FOR DAY TO DAY WORK

Introduction

Microsoft Excel is a spread sheet developed by Microsoft for Windows, macOS, Android and iOS. It is an industry leading spreadsheet program powerful data visualization and analysis tool. It features calculation, graphing tools, pivot tables, and a macro programming language called Visual Basics for Applications.VLOOKUP is one of the best formulas used in MS Excel worldwide.

BASIC MS EXCEL FORMULAS

 

The below are the most common used BASIC EXCEL FORMULAS

 

1.SUM.

The sum function used to aggregate values from a selection of columns or rows of a selected range. Formula is =SUM(RANGE)

2.Average.

The average function used to calculate the average vale of a particular selected range. Formula is =AVERAGE(RANGE)

3.COUNT.

The Count function used to calculate the total numeric numbers count of a particular selected range. Formula is =COUNT(RANGE)

4.COUNTA.

The COUNTA function used to count the total entries in particular selected range.IT counts even dates, texts, numbers etc.

Formula is =COUNTA(RANGE)

 

5.IF

The IF function is often used to sort the data according to given logic. The best part of this formula is we can embed formulas and function in it.

Formula is =IF (Logical test, “Value IF True”, “Value IF False”)

 

6.TRIM

The TRIM function ensures the functions do not return errors because of unnecessary spaces. It makes sure that all unnecessary spaces are eliminated. One of the limitations of this function is, this operates in a single cell.

Formula is =TRIM(TEXT)

 

7.MAX & MIN

The MAX & MIN functions help us to find the maximum number and minimum number in a particular selected range of values.

Formula is =MAX(RANGE)

                    =MIN(RANGE)

8.CONCATENATE.

The CONCAT function is used to merge multiple cells.

Formula is =CONCATENATE (CELL 1, CELL2….etc)

 

9.TODAY

The TODAY function is used to update the daily date automatically.

Formula is =TODAY().Instead of typing the date manually this formula can be used. We do not have to put a value inside the brackets.

 

 

VLOOKUP is one of the best formulas used in MS Excel worldwide, the function reduces time wastage of the traditional ‘COPY & PASTE’ method. VLOOKUP is a very powerful & flexible tool that is used everywhere in MS Excel. There are multiple ways to use the VLOOKUP function.

 

What Is VLOOKUP?

VLOOKUP is very powerful tool to retrieve data from a table and bring back to the working sheet. VLOOKUP is for 'Vertical Lookup'. It is a function which makes MS Excel search for a particular value in a column (It Is called 'table array'), in order to return a value from a different column in the same row.

How does VLOOKUP work?

 

The VLOOKUP function usually 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.

 

In very simple, the VLOOKUP function says: =VLOOKUP (What you want to look up, where you want to look for it, the column number in the range containing the value to return, return an Approximate or Exact match – indicated as 1/TRUE, or 0/FALSE)

 

There are multiple ways to VLOOKUP function in MS Excel. The advanced technique of using VLOOKUP can save working time.

There are mainly 10 ways to use VLOOKUP. They are,

 

1.Normal VLOOKUP

2.VLOOKUP from different sheets.

3.VLOOKUP from different workbook.

4.VLOOKUP formula copying

5.VLOOKUP multiple values at once

6.VLOOKUP smallest value

7.VLOOKUP largest value

8.VLOOKUP exact match

9.VLOOKUP Approximate Match

10.VLOOKUP in range

 

There is a major limitation of VLOOKUP is that it cannot look to the left. The values to lookup must always be on the left-most column of the range and the values to return must be on the right side.

 

 

 

The above-mentioned EXCEL FUNCTIONS are really necessary for a person who is working with DATA in any organization.