2.1. LOOKUP & REFERENCE - VLOOKUP PART 3

Hello Excel-lent,

In our last discussion on VLOOKUP,  we talked about VLOOKUP and its cousins HLOOUP, MATCH, INDEX and CHOOSE and also about many of us been used to One-column VLOOKUP. click here if you missed it please http://excelgist.blogspot.com.ng/2015/09/21-lookup-reference-vlookup-part-2.html

Now let's see how we can use VLOOKUP for a 2-way lookup in the below example. This example teaches you how to lookup a value in a two-dimensional range. We use the MATCH and INDEX function. Below you can find the sales of different ice cream flavors in each month.

1. To find the row number of Feb, use the MATCH function

2. To find the column number of Chocolate, use the MATCH function.

3. To find the sales of Chocolate in February, use the INDEX function.

4. Put it all together.

You can try replace the vanilla products using 3 Nigerian banks and see how it comes out, changing the months and the products or bank names as you wish to see the effect of the 2 column lookup.

We would not have achieved this using the VLOOKUP function alone, many thanks to it's cousins; MATCH & INDEX

Be good, be excel-lent!

Oladapo Sorinola

07014282477, 07062932708BB Pin 52E9802D

Excel is 30!!! – what was your earliest memory of Excel?

Hello Excel-lent,

Last week Excel turned 30. As per wikipedia,

Microsoft released the first version of Excel for the Macintosh on September 30, 1985

Let's celebrate 30 years of Excel by reliving your first memory of it.

I will go first,

My first memory of Excel is from 2004. I just finished my accounting degree and assisted my department in building a simple automated "Result Manager" using Excel 2007.

The current result was manual and many student had suffered from its status as repeated carried over courses in lower levels where still reported as "FAILED" at final year even when students had retaken and "PASSED" those courses. I wanted to help and ensure students are not delayed or denied NYSC so i decided to automate the current result, starting from scratch. This was when I first used Excel, fully.

I made a spreadsheet with all the students details from Matric number to student names, courses and grades in details and worked on it all night. This was same year GLO started "mid night call promo" and most times when talking to my girlfriend at night for hours, while dozing off, I end up mumbling words like "CONCATENATE, VLOOKUP, IF, SUMIFS and bla bla bla...those words off-course got us "MATCHED". Her name is DUPE and I am DAPO. Collect like terms if you like.....

Here is a brief history of Microsoft Excel, in a visual time line

Thanks to her (and many other things), I got paid for the little assistance and had since been "locked up" with Excel. I have designed payrolls and created Excel based reports and dashboards using VBA by fetching data from SQL databases and other data sources. This love for Excel made my life sweeter as an applications/ERP consultant. Today, I implement the 3rd largest global ERP application "INFOR SUNSYSTEMS"  and also provide deep training. 

What about you? What was your earliest memory of Excel? Please share in the comments or send a mail to me at sorinola@findataconsulting.com.

We are currently running a promo where you save at least NGN 2 Million from free implementation of Infor SunSystems as shown below. Take advantage!

Be Excel-lent.

Oladapo Sorinola

BB PIN 52E9802D

07014282477, 07062932708