🚀 Excel Interview Questions with Answers — Part 4

31. How do you use VLOOKUP and what are its limitations?
VLOOKUP searches for a value in the first column of a table and returns a value from another column.

Syntax:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Example:
=VLOOKUP(A2,D2:F10,3,FALSE)

Meaning:
• Search value in A2
• Look in table D2:F10
• Return value from 3rd column
• FALSE = exact match

📌 Example: Find employee salary using employee ID.

Limitations of VLOOKUP:
❌ Searches only left to right
❌ Column number must be counted manually
❌ Breaks if columns are inserted/deleted
❌ Slower on very large datasets

✅ Modern alternative: XLOOKUP or INDEX + MATCH

32. How do you use HLOOKUP?
HLOOKUP searches horizontally across rows.

Syntax:
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

Example:
=HLOOKUP("Jan",A1:F3,2,FALSE)

📌 This searches for "Jan" in the first row and returns data from the second row.

Use Case:
Monthly reports where months are arranged horizontally.

33. How do you use INDEX + MATCH for flexible lookups?
INDEX + MATCH is more flexible and powerful than VLOOKUP.

MATCH
Finds position of a value.
=MATCH(A2,D2:D10,0)

INDEX
Returns value from a position.
=INDEX(E2:E10,3)

Combined Formula:
=INDEX(E2:E10,MATCH(A2,D2:D10,0))

📌 Example: Lookup salary using employee ID.

Advantages:
✅ Can lookup left or right
✅ More dynamic
✅ Safer when columns change
✅ Faster on large datasets

34. How do you use XLOOKUP (if available)?
XLOOKUP is the modern replacement for VLOOKUP.

Syntax:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])

Example:
=XLOOKUP(A2,D2:D10,E2:E10,"Not Found")

📌 Searches employee ID and returns salary.

Advantages:
✅ Searches both directions
✅ No column counting
✅ Built-in error handling
✅ Easier syntax
✅ More efficient

35. How do you use IF with AND / OR conditions?
AND
Returns TRUE only if all conditions are true.

Example:
=IF(AND(A1>=50,B1>=50),"Pass","Fail")

📌 Student passes only if both subjects are above 50.

OR
Returns TRUE if any condition is true.

Example:
=IF(OR(A1>=90,B1>=90),"Bonus","No Bonus")

📌 Bonus given if any score exceeds 90.

36. How do you use SUMIF, SUMIFS for conditional sums?
SUMIF
Adds values based on one condition.

Syntax:
=SUMIF(range,criteria,sum_range)

Example:
=SUMIF(A:A,"East",B:B)

📌 Sums sales only for East region.

SUMIFS
Handles multiple conditions.

Example:
=SUMIFS(C:C,A:A,"East",B:B,"Laptop")

📌 Sums laptop sales in East region.

37. How do you use COUNTIF, COUNTIFS for conditional counts?
COUNTIF
Counts cells matching one condition.
=COUNTIF(A:A,"Yes")

📌 Counts all “Yes” entries.

COUNTIFS
Counts using multiple conditions.
=COUNTIFS(A:A,"East",B:B,"Laptop")

📌 Counts laptop orders in East region.

38. How do you use AVERAGEIF, AVERAGEIFS?
AVERAGEIF
Calculates average based on one condition.
=AVERAGEIF(A:A,"East",B:B)

📌 Average sales in East region.

AVERAGEIFS
Uses multiple conditions.
=AVERAGEIFS(C:C,A:A,"East",B:B,"Laptop")

📌 Average laptop sales in East region.

39. How do you use COUNTBLANK, COUNTUNIQUE-style techniques?
COUNTBLANK
Counts empty cells.
=COUNTBLANK(A1:A20)

📌 Useful for finding missing data.

Count Unique Values (Older Excel):
=SUM(1/COUNTIF(A1:A10,A1:A10))

Modern Excel:
=COUNTA(UNIQUE(A1:A10))

📌 Counts unique customers/products/etc.

40. How do you use SUMPRODUCT for weighted calculations?
SUMPRODUCT multiplies arrays and returns the total.

Basic Example:
=SUMPRODUCT(A1:A5,B1:B5)

📌 Multiplies corresponding values and sums them.

Weighted Average Formula:
Weighted Average = SUM(xᵢ * wᵢ) / SUM(wᵢ)

Double Tap ❤️ For Part-5