The Excel Bible

Breaking down chapters 1, 2 and 3 of the ALMIGHTY EXCEL BIBLE

Brief introduction of the topic

Cheers to 889 pages of Excel Jargons. It is almost impossible to read the excel handout, especially as the faculty has mandated that we read all the chapters. In the following weeks, I will be working on the excel bible, and it will be at least 3 chapters.

Chapter 1 – Range Names

How can I create range names?

1. By entering a range name in the Name box.
2. By clicking Create From Selection in the Define Names group on the Formula tab.
3. By clicking Name Manager or Define Name in the Defined Names group on the Formulas tab.

Chapter 2 – Lookup Functions

Syntax of the lookup functions

Lookup functions enable you to look up values from worksheet ranges. In Microsoft Excel 2013, you can perform both vertical lookups (by using the VLOOKUP function) and horizontal lookups (by using the HLOOKUP function). In a vertical lookup, the lookup operation starts in the first column of a worksheet range.

VLOOKUP Syntax

1. Lookup value is the value you want to look up in the first column of the table range
2. Table range is the range that contains the entire lookup table. The table range includes the first column, in which you try ti match the lookup value, and any other columns in which you want to look up formula results.
3. Column index is the column number in the table range from which the value of the lookup function is obtained
4. Range lookup is an optional argument. The point of range lookup is to specify an exact or approximate match. If the range lookup argument is True or omitted, the first column of the table range must be in ascending numerical order.

HLOOKUP syntax

In an HLOOKUP function, excel tries to locate the lookup value in the first row (not the first column) of the table range. Basically, for an HLOOKUP function, use the VLOOKUP syntax and change column to row.

Chapter 3 – INDEX function

Syntax of the INDEX function

The INDEX function enables you to return the entry in any row and column within an array of numbers. The most commonly used syntax for the INDEX function is the following:

INDEX (Array, Row, Number, Column Number).