Category Archives: Tutorials

Advanced Lookup Functions Combinations: Part 2

Kornilios Ampatzis/ April 17, 2019/ Tutorials

We will continue our quest for the best lookup formula with another combination. We will use an OFFSET and two MATCH functions to do a matrix lookup. Unfortunately, this formula also only searches from the right column to the left and from the top to the bottom, but is very easy to understand and use.

Advanced Lookup Functions Combinations: Part 1

Kornilios Ampatzis/ April 17, 2019/ Tutorials

We will begin a quest to find the best lookup function. All the internal functions of Excel have limitations which we will try to overcome. In this and the following tutorials, the knowledge of the simple syntax of the functions mentioned (VLOOKUP, HLOOKUP, MATCH), is considered a requirement.

How to use the LOOKUP Function

Kornilios Ampatzis/ April 17, 2019/ Tutorials

From all the lookup functions, LOOKUP is the least useful. Even though it is a powerful function, it is hardly used at all.

How to use the HLOOKUP Function

Kornilios Ampatzis/ April 17, 2019/ Tutorials

When you start learning about the lookup functionality of Excel, HLOOKUP will probably be one of the first functions you will stumble upon.

How to use the VLOOKUP function

Kornilios Ampatzis/ April 17, 2019/ Tutorials

When you start learning about the lookup functionality of Excel, VLOOKUP will probably be one of the first functions you will stumble upon.

How to use the OFFSET function

Kornilios Ampatzis/ April 17, 2019/ Tutorials

One very useful Excel function is OFFSET. It returns a range of cells which corresponds to a specified number of rows and columns starting at a given distance from a reference cell.

How to use the MATCH function

Kornilios Ampatzis/ April 17, 2019/ Tutorials

There is no way to create a powerful and versatile lookup formula without using at least once the MATCH function.

How to use the INDEX function

Kornilios Ampatzis/ April 17, 2019/ Tutorials

The INDEX function is one of the most interesting functions in excel. It can be combined with many other functions to help you out of some difficult situations.

How to use the CHOOSE function

Kornilios Ampatzis/ April 17, 2019/ Tutorials

Probably the easiest to understand lookup function is the CHOOSE function. It returns a value from an array of values, that corresponds to a specific position in that array.

Hierarchies with PowerPivot in Excel

Kornilios Ampatzis/ April 17, 2019/ Tutorials

Let us take the next step in power pivot tables. We will talk about hierarchies in Power Pivot and will show a bit more of its functions.