excel single blog pageExcel spreadsheets are widely used to represent different information and data.

Many basic Excel tips and tricks are known by almost everyone, but there a few of them,  which are not so common and will make you to step up, feel special and showof what you are capable of.

1. VLOOKUP

Most likely some of you are already  familiar with  the Excel function VLOOKUP.

VLOOKUP is a function that allows you to find a certain value in your database tables/lists.

Works quite simply in a few steps:

  • Choose a table/list that you need.
  • Insert in the formula sheet the value that you will be looking for and select the column to return.

To make it more clear here’s an example about eggs discount price

Formula Results
Formula Results

Steps:

  • Select the Vlookup function
  • In the formula type the value you want to look for (in our case discount price of eggs)
  • Select your search array (in this case A2:CC5)
  • Enter the column you want to return (in this case we are looking for the discount price of eggs, so we want to enter column 3 in our (A2:CC5) array)
  • We choose to type zero “0” in our range. This will work with all unsorted data
Formula sheet
Formula

Vlookup is a really great tool to use in many cases, however it has some downfalls:

  • It is limited, as you can only do your search from left to right.  If I wanted to find any value to the left, I would need to change around all my spreadsheet.
  • If you are working with huge spreadsheets it can be difficult and time consuming to select all search arrays.

2. INDEX AND MATCH

To make this all process simple and easier I want to introduce two other useful functions INDEX AND MATCH.

2.1 INDEX

INDEX (ARRAY, N) from your chosen array it will return to your selected n value.

For example we want to find out who is the 3rd ranked person by donations.

Formula
Formula
Result
Result

Steps:

  • We choose an array. In this case first column will be our array (A4:A9) as we are searching for a person name who is the 3rd ranked person by donations.
  • For n value in this case we choose (4), as we interested to find out about charity donations and column 4 represents it.
Formula sheet
Formula


2.2 MATCH

MATCH function (VALUE, ARRAY, RANGE) returns index of the value inside chosen array.

For example we want to find the index of Patvelik Ltd.

Formula
Formula
Result
Result

Steps:

  • Lookup value is Patvelik Ltd (B7) as we interested to find the index of Patvelik Lt
  • Lookup array (B4:B9)
  • Match type (0). It finds the first value which is identical to look up value.
Formula sheet
Result

What is great about these two INDEX AND MATCH functions?

  •  On its own when used separately, these functions are not really powerful.
  • Even though MATCH is good to use if you want to find the position of the specific item rather than the item itself.
  • On other hand if you combine these two formulas together, it becomes way stronger and more useful tool than just Vlookup.

2.3 Combined Power

Power comes with combined formula

=INDEX (Column, MATCH (Search value, search column, 0))

For example I want to find who is director of Humu&Jones.

It won’t be possible to find out using Vlookup, as if you remember this function only looks for a value from left to right.

Formula
Formula
Result
Result

Steps:

  • MATCH function helps me to find index of Humu&Jones.
  • Meanwhile INDEX uses this information to find and return the matching value. Which is Luisa Cernila

There is one more example to show you how using these formulas together you can swim in excel like fish in water!

It will perfectly work with huge databases while VLookup will have problems to deal with.

Formula
Formula
Formula
Formula
Result
Result