Here, A2:C13 is the column of data I want the formula to return, Jan is the value I want to match, A2:A13 is the column in which the formula will find Jan and the 0 signifies that I want the formula to find an exact match for the value.

Whether you have to analyze your personal finance or any large data set, its the functions that make the job easy.

However, finding the right function for your data set can be very tricky.

Heres a list of some essential Microsoft Excel functions that you can use for data analysis and you can boost your productivity in the process.

Note: For the uninitiated, the functions mentioned in this post need to be added in the Formula Bar of an Excel spreadsheet containing data or within the cell in which you want the result.

The function is particularly useful for combining data from different cells into a single cell.

For instance, it comes handy for creating the tracking parameters for marketing campaigns, build API queries, add text to a number format, and several other things.

LEN LEN is another handy function for data analysis that essentially outputs the number of characters in any given cell.

The function is predominantly usable while creating title tags or descriptions that have a character limit.

It can also be useful when youre trying to find out the differences between different unique identifiers which are often quite lengthy and not in the correct order.

For this, I made use of the formula LEN(C2) in the cell D2 to get 5 as the result.

VLOOKUP VLOOKUP is probably one of the most recognizable functions for anyone familiar with data analysis.

The function offers two modes of matching exact and approximate, which is controlled by the range of the lookup.

If you set the range to FALSE, itll look for an exact match, but if you set it to TRUE, itll look for an approximate match.

For that, I used the formula VLOOKUP(Jun, A2:C13, 3) in the cell G4 and I got 74992 as the result.

Here, Jun is the lookup value, A2:C13 is the table array in which Im looking for Jun and 3 is the number of the column in which the formula will find the corresponding views for June.

The only downside of using this function is that it only works with data that has been arranged into columns, hence the name vertical lookup.

So if youve got your data arranged in rows, youll first need to transpose the rows into columns.

Formula: VLOOKUP( lookupvalue, tablearray, colindexnum, rangelookup ) 4.

INDEXMATCH Much like the VLOOKUP function, the INDEX and MATCH function come in handy for searching specific data based on an input value.

So when you combine these two functions, they can pinpoint the data reference and search for a value in a single dimension array.

For that, I used the formula INDEX (A2:C13, MATCH(Jan, A2:A13,0), 3).

Data Analysis For Mac In Excel