The 2 Most Important
Lesson 7: Excel Spreadsheets Lookup and Reference Functions (18)
In annex 7 you have found a description of all 18 Excel functions in the "LookUp and Reference" category. Below is the list of the 5 most useful ones. See more on the very powerful INDEX/MATCH Excel formulas in lesson 12 and in the three old lookup functions in lessons 17, 18 and 19: HLOOKUP function and VLOOKUP function.
The most important functions in this category
See lesson 12 on INDEX/MATCH
The LOOKUP Group
The functions in this group are widely known among advanced users. But once they discover the more powerful and less limited INDEX/MATCH they are kind of pushed aside. Click on the links below to access the pages of this website describing how they work and what is their limits.
Excel Lesson 17 - Excel HLOOKUP Function
When you start developing more complex business models or when you want to calculate and chart moving averages and moving " Year to Date" you will need the two following functions.
The general format of this function goes as follows:
Tutorial and Examples
With INDEX and MATCH you can semi automate your invoices so that when you enter the name of a client it's address appears in the cell below and when you enter the product number it's description appears in the cell to the right.
SUMPRODUCT ¦ INDEX/MATCH ¦ SUBTOTAL ¦ ISERROR ¦ HLOOKUP ¦ LOOKUP ¦ VLOOKUP
AVERAGEIF ¦ AVERAGEIFS ¦ COUNTIF ¦ COUNTIFS ¦ SUMIF ¦ SUMIFS ¦ IFERROR