2. Using Functions- LOOKUP & REFERENCE

Hello Excel-lent,

Today's traffic is what Prof. Wole Soyinka would call "mysterious millipede, moving towards a wailing wake-keep". Unfortunately, I cannot help you resolve these traffic issues within the metropolis but here I am to help you with the mostly used formulas in your everyday use of Microsoft Excel.

Someone recently asked in the Financial and Modelling group in Linkedin that " what function(s) of Excel do you use most?" and one word kept winning the day- VLOOKUP! 

In my years of excel experience as a data analyst, there is no way you can excuse yourself of the function "VLOOKUP" but you can garnish your data more and slice them deep and quicker with the introduction of other flavours like HLOOKUP, MATCH, INDEX, ROWS, COLUMNS, INDIRECT etc.

First, let us get to understand what each of these functions does.

ADDRESS      -    Returns a reference as text to a single cell in a worksheet

AREAS              -    Returns the number of areas in a reference

COLUMN      -    Returns the column number of a reference

COLUMNS      -    Returns the number of columns in a reference

HLOOKUP      -    Searches for a value in the top column of a table and then returns a value in the same                                     column from a row you specify in the table

HYPERLINK      -   Creates a shortcut that opens a document on your hard drive, a server, or the Internet

INDIRECT      -   Returns a reference indicated by a text value

MATCH              -   Returns the relative position of an item in an array

OFFSET              -   Returns a reference offset from a given reference

ROWS              -   Returns the number of rows in a reference

RTD                      -   Returns real-time data from a program that supports COM automation

GETPIVOTDATA -   Returns data stored in a PivotTable

CHOOSE       -   Chooses a value from a list of values

INDEX               -   Uses an index to choose a value from a reference or array

LOOKUP       -   Returns a value either from a one-row or one-column range or from an array

ROW               -   Returns the row number of a reference

TRANSPOSE      -    Returns the transpose of an array

VLOOKUP      -   Searches for a value in the leftmost column of a table and then returns a value in the                                       same row from a column you specify in the table

Interestingly, I use a combo of INDEX and MATCH to do the trick when I need to lookup towards the right which VLOOKUP cannot achieve.

Join me tomorrow as we begin to slice and dice your data with hundreds of Excel formulas and functions.

Join Excelgist today to receive excel discussions straight in your mail box and do share this post with your colleagues.

Excel with Excel!

Excel-lently yours,
Oladapo Sorinola
07014282477, 07062932708
BB Pin 52E9802D