Excel Report - 10 Lookup Formulas You Need to Extract Answers From Flat Tables

Creating reports for managers and customersthe Sales made by George in August
from flat files the whole day, requires your7. COUNTIF It allows you to count various lines
concentration and creativity. Hopefully, you arethat match one condition. For example: count all
equipped with the easiest and most flexible Datathe Yes Answers
analysis software available in the market, Excel.8. COUNTIFS It allows you to count various lines
However, you spend hours trying to build thethat match multiple conditions, these conditions
formula that will extract the data or trying to findare evaluated in multiple columns. For example:
the direct formulas to do it.count all the Yes Answers of Female respondents
You have the software but you should know how9. AVERAGEIF It allows you to average various
to use the relevant tools for your assignment.lines that match one condition. For example:
Excel has more than 300 functions, how do youaverage all the Sales made by George
know which ones are relevant to create Excel10. AVERAGEIFS It allows you to average various
Reports?lines that match multiple conditions, these
You don't need to struggle anymore...conditions are evaluated in multiple columns. For
All the formulas you need are in the Excel Lookupexample: average all the Sales made by George in
Toolbox, a compilation of 10 most potent lookupAugust
techniques in Excel to help you crunch multipleAs you can see, by using the above formulas you
lines and multiple columns into a single cell, the cellwill be able to craft the reports no matter the
you want.questions, no matter the style of presentation.
Let me show them briefly...Where you have a cell, it could be an answer.
In summary you need formulas that will answer
1. VLOOKUP It allows you to search a value in athe unusual questions that the manager or the
backend index column and to retrieve thecustomers have. You do it by crunching multiple
corresponding value from other column. It onlycolumns and multiple lines and putting the queried
searches values on the left and retrieves valuesvalue in the proposed cells.
to the right. For example: search the ProductFor example: Esther Low, a finance assistant from
Code and return the Product PriceSingapore is now able to work with tables and
2. INDEX & MATCH It has the samelook into ranges of ages, salary levels that have
VLOOKUP functionality but it is not limited todifferent rate computation to calculate the staff's
search only the left columnrequired CPF contribution set by the government,
3. Array Formulas It allows you to search multipleetc.
columns and complex computations. For example:Conclusion.
search the Orders made by Susan in August andThe most important reason I use Lookup
return the totalformulas is because I can present only the
4. SUMPRODUCT It is a kind of Array Formulainformation that is expected by end-users and I
but more readablecan control cell inputs and see instant changes.
5. SUMIF It allows you to sum various lines thatThis is no a land of Pivot Tables
match one condition. For example: sum all theDon't lose the opportunity to attend report
Sales made by Georgerequirements, and to enjoy the sense of
6. SUMIFS It allows you to sum various lines thatcommitment when you identify a business trend
match multiple conditions, these conditions arethat is built on your query in a cell.
evaluated in multiple columns. For example: sum all