Part 3: Advanced Excel Formulas Quiz – IF, XLOOKUP, SUMIFS (20 MCQ)

Correct answer: B. IF

★ Key Takeaway: IF is Excel’s “decision” function—it helps you automate rules.

Explanation: IF checks a condition like A1>=60. If the condition is TRUE, it returns one result; if FALSE, it returns a different result. For example, =IF(A1>=60,"Pass","Fail") automatically labels results without manual checking.

Why other options are incorrect:

• A. SUM – SUM adds numbers together, but it does not test a condition to choose between two results.
• C. COUNT – COUNT counts numeric cells, but it does not do decision-making or return different outputs based on TRUE/FALSE.
• D. AVERAGE – AVERAGE finds the mean of numbers, but it does not evaluate conditions.

Tip: IF and nested IF are the “gateway” to real Excel skills. If the logic feels confusing, guided practice makes it click fast. Explore 1-on-1 Excel tutoring on Fiverr →

Correct answer: A. =IF(A1>=90,"High",IF(A1>=70,"Medium","Low"))

★ Key Takeaway: Nested IF helps you classify values into multiple levels (not just two).

Explanation: Excel checks the first rule (A1 is 90 or more). If that is TRUE, it returns "High" immediately. If it is FALSE, Excel checks the next rule (A1 is 70 or more) and returns "Medium" if TRUE; otherwise it returns "Low". This “check from highest to lowest” order prevents wrong results.

Why other options are incorrect:

• B. =IF(A1>=90,"High",IF(A1>=70,"Low","Medium")) – The labels are mixed up, so a score like 75 would incorrectly return "Low" instead of "Medium".
• C. =IF(A1>=90,"High","Medium","Low") – IF can only return one TRUE result and one FALSE result, so it cannot handle three categories in a single IF like this.
• D. =IF(A1>=70,"Medium",IF(A1>=90,"High","Low")) – The order is wrong; a score like 95 makes A1>=70 TRUE first, so Excel returns "Medium" and never reaches the "High" rule.

Correct answer: C. =IF(AND(A1>=18,B1="Yes"),"Eligible","Not Eligible")

★ Key Takeaway: Use AND when every rule must be true at the same time.

Explanation: AND checks both conditions together and returns TRUE only if both are true. Then IF uses that TRUE/FALSE result to return a clear label. This is common in real work: approvals, eligibility, compliance checks, and filtering who qualifies.

Why other options are incorrect:

• A. =IF(OR(A1>=18,B1="Yes"),"Eligible","Not Eligible") – OR allows either condition to be true, so someone under 18 could become "Eligible" if B1 is "Yes".
• B. =AND(A1>=18,B1="Yes","Eligible","Not Eligible") – AND does not return custom text results like "Eligible"; it returns only TRUE or FALSE.
• D. =IF(A1>=18,B1="Yes","Eligible","Not Eligible") – IF’s second argument should be the TRUE result, not another condition test.

Correct answer: C. IFERROR

★ Key Takeaway: IFERROR replaces messy error messages with a clean result you control.

Explanation: Many formulas can produce errors (like #DIV/0! or #N/A). IFERROR lets you return something friendly instead, such as a blank ("") or text like "Not Found". Example: =IFERROR(A1/B1,"") keeps your sheet readable when B1 is zero.

Why other options are incorrect:

• A. ISERROR – ISERROR only checks if something is an error (TRUE/FALSE); it does not automatically replace the error with a custom value.
• B. ERRORCHECK – ERRORCHECK is not a standard Excel worksheet function.
• D. IFBLANK – IFBLANK is not a standard Excel worksheet function name; Excel uses IF with ISBLANK instead.

Correct answer: A. In the first (leftmost) column of the table_array

★ Key Takeaway: VLOOKUP can only search in the first column of the table you select.

Explanation: VLOOKUP searches vertically down the first column of your table_array to find the lookup value, then returns data from a column to the right. This is why VLOOKUP often fails when your “key column” is not the first column.

Why other options are incorrect:

• B. In any column of the table_array – VLOOKUP does not search any column; it searches only the leftmost column for matching.
• C. In the last column of the table_array – The lookup value can be returned from the last column, but it cannot be searched there.
• D. Only in the header row of the table_array – VLOOKUP matches against data rows; headers are labels, not the typical lookup target.

Correct answer: C. Return the value from the second column of the table_array

★ Key Takeaway: col_index_num tells VLOOKUP which column to return from the selected table.

Explanation: In VLOOKUP, the column number is counted from the left side of the table_array. So if your table is D:F, then D is column 1, E is column 2, and F is column 3. That’s why “2” returns from column E.

Why other options are incorrect:

• A. Look in the second row of the table – VLOOKUP does not use this argument to choose a row; rows are based on where the match is found.
• B. Find the second match in the first column – VLOOKUP returns the first matching row; it is not designed to return the “second match.”
• D. Use approximate matching – Approximate matching is controlled by the last argument (TRUE/FALSE), not the column number.

Correct answer: D. =INDEX(C:C, MATCH(E1, A:A, 0))

★ Key Takeaway: MATCH finds the position; INDEX returns the value from that position.

Explanation: MATCH(E1, A:A, 0) finds the row where E1 appears in column A using exact matching. INDEX then uses that row number to return the value from column C. This method is flexible because you can “look left” or “look right” depending on which column INDEX returns from.

Why other options are incorrect:

• A. =INDEX(A:A, MATCH(E1, C:C, 0)) – This searches for E1 in column C and returns from column A, which is the opposite of what the question asks.
• B. =MATCH(C:C, INDEX(E1, A:A, 0)) – This is not valid syntax; MATCH needs a lookup value and lookup range, and INDEX needs a range, not E1 as the range.
• C. =INDEX(C:C, MATCH(A:A, E1, 0)) – MATCH arguments are reversed; you must search a range for a value, not search a value for a range.

Correct answer: B. 0

★ Key Takeaway: Use MATCH with 0 when you need a perfect match (most common in real work).

Explanation: match_type 0 tells Excel to find the exact lookup value in the range. If it does not exist, Excel returns #N/A. This behavior is important because it prevents Excel from “guessing” the nearest match.

Why other options are incorrect:

• A. 1 – This is approximate matching and typically requires data sorted ascending.
• C. -1 – This is approximate matching for descending sorted data, not exact matching.
• D. 2 – 2 is not a valid match_type value in MATCH.

Correct answer: B. XLOOKUP

★ Key Takeaway: XLOOKUP is a flexible lookup tool because you choose both the lookup range and the return range.

Explanation: VLOOKUP usually needs the lookup value in the first column of the table and returns only to the right. XLOOKUP is easier because it lets you search one range (like an ID column) and return from any other range (like a name or price column), even if it is on the left. This makes your formulas stronger when columns move or tables change.

Why other options are incorrect:

• A. HLOOKUP – HLOOKUP searches across rows (horizontal), not down columns, and it does not solve the common “return from any column” problem the same way XLOOKUP does.
• C. SUMIFS – SUMIFS adds numbers based on conditions; it is not a lookup function and does not return a matched value from another column.
• D. COUNTIF – COUNTIF counts how many cells match a condition; it does not retrieve related data from another column.

Tip: Lookups feel hard until you practice with real tables. A tutor can guide you through XLOOKUP and INDEX/MATCH step by step. Find an Excel tutor on Fiverr →

Correct answer: D. SUMIFS

★ Key Takeaway: SUMIFS is used when you need conditional totals with more than one rule.

Explanation: SUMIFS adds numbers only for rows that match all your criteria. This is extremely common in real reporting, like summing sales for a specific region, month, and product type without filtering the table manually.

Why other options are incorrect:

• A. SUMIF – SUMIF supports only one condition, so it cannot handle multiple rules at the same time.
• B. SUM – SUM adds all values and ignores conditions completely.
• C. COUNTIFS – COUNTIFS counts matching rows; it does not sum numeric totals.

Correct answer: D. COUNTIFS

★ Key Takeaway: COUNTIFS answers “How many match all these rules?”

Explanation: COUNTIFS applies two or more conditions (like Region="East" and Status="Completed") and counts only the rows that meet every condition. It’s used in dashboards, quality checks, and KPI summaries where you need filtered counts quickly.

Why other options are incorrect:

• A. COUNTIF – COUNTIF counts with only one condition, so it cannot test multiple criteria together.
• B. COUNTA – COUNTA counts non-empty cells and does not apply conditions like “East” or “Completed”.
• C. AVERAGEIFS – AVERAGEIFS calculates an average with multiple conditions; it does not count rows.

Correct answer: C. AVERAGEIFS

★ Key Takeaway: AVERAGEIFS is for conditional averages with more than one rule.

Explanation: AVERAGEIFS helps answer questions like “What is the average sale amount for East region in January?” without filtering the dataset. It averages only the rows that match all the criteria you specify.

Why other options are incorrect:

• A. AVERAGE – AVERAGE averages everything in the range and does not filter by conditions.
• B. AVERAGEIF – AVERAGEIF supports only one condition, not multiple conditions.
• D. COUNTIFS – COUNTIFS counts matching rows; it does not calculate an average.

Correct answer: B. TRIM

★ Key Takeaway: TRIM is a simple cleaning tool that fixes messy spacing in text data.

Explanation: TRIM removes leading spaces, trailing spaces, and extra spaces between words. This matters because extra spaces can break lookups and comparisons—“Apple” and “Apple ” can look the same to a human, but Excel treats them as different text.

Why other options are incorrect:

• A. CLEAN – CLEAN removes non-printable characters, but it does not specifically fix extra spaces between words like TRIM.
• C. TEXTJOIN – TEXTJOIN combines text from multiple cells; it does not remove extra spaces from an existing text string.
• D. PROPER – PROPER changes capitalization (like “john” to “John”); it does not remove extra spaces.

Correct answer: A. LEFT

★ Key Takeaway: LEFT is used to extract the beginning part of text—very useful in data cleaning.

Explanation: LEFT(text, num_chars) takes characters from the start of a text value. For example, if an ID is “BD-1025”, =LEFT(A1,2) returns “BD”. People use this to split codes, clean imported data, and build categories.

Why other options are incorrect:

• B. RIGHT – RIGHT extracts characters from the end of the text, not from the left side.
• C. MID – MID extracts characters from the middle of the text based on a start position, not specifically from the left side.
• D. LEN – LEN returns how many characters are in the text; it does not extract characters.

Correct answer: B. TEXTJOIN

★ Key Takeaway: TEXTJOIN is a smart way to merge text with separators like commas, spaces, or hyphens.

Explanation: TEXTJOIN lets you choose a delimiter and combine many values at once, such as joining first name and last name across multiple cells. For example, TEXTJOIN(", ", TRUE, A1:A5) can create a clean comma-separated list while ignoring blanks.

Why other options are incorrect:

• A. CONCAT – CONCAT joins text, but it does not automatically insert a delimiter between items like TEXTJOIN does.
• C. TRIM – TRIM removes extra spaces; it does not join text from multiple cells.
• D. VALUE – VALUE converts text numbers into real numbers; it does not combine text.

Correct answer: A. ROUND

★ Key Takeaway: ROUND is used when you want clean, consistent decimal values.

Explanation: ROUND(number, num_digits) rounds a value normally, like 12.345 to 12.35 when rounding to 2 decimals. This is important in money, grades, and reports where long decimals look messy or cause confusion.

Why other options are incorrect:

• B. INT – INT rounds down to the nearest whole number and does not let you choose decimal places.
• C. TRUNC – TRUNC removes decimals without rounding; it cuts the number instead of rounding it.
• D. ABS – ABS removes the negative sign; it does not round the number.

Correct answer: A. TODAY

★ Key Takeaway: TODAY gives the current date and refreshes automatically.

Explanation: TODAY returns only the date (no time), and it updates when the workbook recalculates. It is commonly used for tracking deadlines, calculating age, or showing “days left” using formulas like TODAY() - start_date.

Why other options are incorrect:

• B. NOW – NOW returns both date and time, not date only.
• C. DATE – DATE builds a date from year, month, and day inputs; it does not automatically return today’s date.
• D. DAY – DAY extracts the day number (like 15) from a date; it does not return the full current date.

Correct answer: B. DATE

★ Key Takeaway: DATE helps you build clean dates from numbers—perfect for imported data.

Explanation: DATE(year, month, day) returns a real Excel date value that you can sort, filter, and calculate with. It is useful when data comes as separate parts, like year in one column and month in another, and you need a proper date for analysis.

Why other options are incorrect:

• A. TEXT – TEXT formats values for display, but it does not create a true date value the same way DATE does.
• C. DATEDIF – DATEDIF calculates the difference between dates; it does not build a date from parts.
• D. EOMONTH – EOMONTH returns the end date of a month based on a start date; it does not build a date from year/month/day inputs.

Correct answer: D. It locks the row and column so the reference does not change when copied

★ Key Takeaway: $ is used for absolute references—one of the most important skills in Excel.

Explanation: When you copy a formula, Excel normally shifts cell references (relative references). $A$1 locks both column A and row 1 so the reference stays fixed. This is essential for tax rates, fixed conversion values, or any “constant” cell used across many rows.

Why other options are incorrect:

• A. It turns the cell into a text value – $ does not change the data type; it only changes how references behave when copied.
• B. It hides the formula from being seen – Hiding formulas is done using cell formatting/protection, not with $ signs.
• C. It converts the value into currency automatically – Currency formatting is done through number formats; $ in a reference is not formatting.

Correct answer: C. They automate decisions, lookups, and conditional calculations so results stay consistent as data changes

★ Key Takeaway: Advanced formulas help you build “smart spreadsheets” that update automatically.

Explanation: When you use functions like IF, IFERROR, SUMIFS, and lookup formulas, your workbook can update results instantly when new data comes in. This saves time, reduces manual work, and helps you produce consistent reports—something employers love.

Why other options are incorrect:

• A. They are mainly used for changing worksheet colors and fonts – Colors and fonts are formatting tools; formulas are for logic and calculation.
• B. They remove the need to store data in rows and columns – Excel still needs data stored in cells; formulas work using that data.
• D. They guarantee no one will ever make mistakes in the workbook – Formulas reduce errors, but mistakes can still happen if inputs are wrong or rules are incorrect.