Here are a few Excel functions, with worked examples that I hope help you master them…

Index

  1. IF
  2. SUMIF
  3. SUMIFS
  4. VLOOKUP
  5. HLOOKUP
  6. INDEX & MATCH
  7. INDIRECT
  8. Adding an Index Page to a Workbook

1. IF function

An IF statement is used to make a choice. If something, then something, otherwise something else.

Download the worked examples in Excel format – IF worked example

Office support link – IF FUNCTION

2. SUMIF function

A SUMIF is useful for adding up a column of data, based on the criteria you want.  If you just want sales for Feb or for one product that are in a list of all other dates / products then you can return just the sum of those (COUNTIF can also be used to count in the same way)

Download the worked examples in Excel format – SUMIF worked example

Office support link – SUMIF FUNCTION

3. SUMIFS function

A subtle difference in function makes this a much more powerful tool.  This allows you to specify multiple criteria when using a SUMIF.

Download the worked examples in Excel format – SUMIFS worked example

Office support link –SUMIFS FUNCTION

4. VLOOKUP function

There are a few different ways to ‘lookup’ data.  The basics are that you are trying to return a value based on a value that you already have.

In this worked example we have a purchase invoice list with corresponding purchase order number and want to know who raised it, when it was raised and when it was authorised.

Download the worked example in Excel format – VLOOKUP worked example

Office support link – VLOOKUP FUNCTION

5. HLOOKUP function

This is a lesser used lookup function that allows you to look vertically down the data, rather than across.

In this worked example we have a detailed report of daily sales by product and want to simple show the sales for the day in another table.

Download the worked example in Excel format – HLOOKUP worked example

Office support link – HLOOKUP FUNCTION

6. INDEX & MATCH function

When combined the Index & Match function perform a turbo charged lookup function.  Allowing you to truly find a needle in a haystack!

In this worked example we have a purchase invoice list with corresponding purchase order number and want to know who raised it, when it was raised, when it was authorised and the quantity ordered.  The big difference here is the column order of the data.  The PO number in the orders table is not the left most column and hence a vlookup wouldn’t work.

Download the worked example in Excel format – INDEX MATCH worked example

Office support link – INDEX MATCH FUNCTIONS

7. INDIRECT function

Use this to substitute part of any formula with a cell reference.

Download the worked example in Excel format – INDIRECT worked examples

Office support link – INDIRECT FUNCTION

It’s pretty versatile and can even be used in things like lookups – Dynamic Lookup Table

8. Add an Index Page to a Workbook

I can’t take credit for this – to see the original please visit How to Excel

This is a way to use the tab names to create a front page, with hyperlinks, as a cool index page.

Basically…

  1. Go to the Formulas tab.
  2. Press the Define Name button.
  3. Enter SheetNames into the name field.
  4. Enter the following formula into the Refers to field.
    =REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")
  5. Hit the OK button.

Step-003-How-To-Generate-A-List-Of-Sheet-Names-From-A-Workbook-Without-VBA How To Generate A List Of Sheet Names From A Workbook Without VBA

In a sheet within the workbook enter the numbers 1, 2, 3, etc. into column A starting at row 2, and then in cell B2 enter the following formula and copy and paste it down the column until you have a list of all your sheet names.

=INDEX(SheetNames,A2)

Step-004-How-To-Generate-A-List-Of-Sheet-Names-From-A-Workbook-Without-VBA How To Generate A List Of Sheet Names From A Workbook Without VBA

As a bonus, we can also create a hyperlink so that if you click on the link it will take you to that sheet. This can be handy for navigating through a spreadsheet with lots of sheets. To do this, add this formula into column C.

=HYPERLINK("#'"&B2&"'!A1","Go To Sheet")

Note: to use this method you will need to save the file as a macro enabled workbook (.xls, .xlsm or .xlsb). Not too difficult and no VBA needed.