The Secret to Excel Expertise: A Complete Guide to Essential Functions

1️⃣ SUMIFS Function

Use it to sum cells that meet multiple criteria.

=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2...)

• SUMIFS is more useful as the number of criteria increases.
• It can handle a variety of criteria, including date conditions and whether text is included.

2️⃣ VLOOKUP Function

Use it to search a table and return a value from a specific column.

=VLOOKUP(lookup_value, table_array, col_index_num, FALSE)

• For an exact match, always use FALSE.
• Limitation: It cannot look to the left → solve this with INDEX+MATCH.

3️⃣ INDEX + MATCH Combination

A powerful combination that overcomes the limitations of VLOOKUP.

=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

• Can look up values in columns to the left.
• The structure remains stable when columns are inserted or deleted.

4️⃣ IF Function

Returns different values depending on a condition.

=IF(logical_test, value_if_true, value_if_false)

• You can nest IF functions to handle multiple conditions.
• Examples include assigning grade levels and calculating amounts by range.

5️⃣ INDIRECT Function

Use it to dynamically create and reference a cell address.

=INDIRECT("A" & B1)

• If B1 is 10, it refers to A10.
• You can dynamically change sheet names and range names, making it useful for automation.

📌 Useful Function Combination Tips for Work

❓ Frequently Asked Questions (FAQ)

• Q1. What is the difference between SUMIF and SUMIFS?
  SUMIF handles one criterion, while SUMIFS can handle multiple criteria.
• Q2. I keep getting VLOOKUP errors!
  Be sure to check for an exact match and verify the reference range.
• Q3. INDIRECT seems slow.
  Using many INDIRECT functions can reduce performance.
• Q4. Isn’t INDEX+MATCH complicated?
  Once you learn it, you can use it much more flexibly.
• Q5. How do I enter multiple conditions at once?
  You can use AND/OR or combinations of SUMIFS and IF.

SUMIFS와 INDIRECT 함수로 조건을 셀 값으로 유연하게 조정하는 방법

INDEX MATCH 함수 기초 설명 및 활용법 (VLOOKUP 여러조건)