This part focuses on advanced formulas, dynamic arrays, lookup problems, and tricky interview scenarios.
1๏ธโฃ What happens if VLOOKUP finds multiple matches?
Sample Answer:
VLOOKUP returns only the first matching result it finds.
If multiple records have the same lookup value, it will not return all matches.
If I need multiple matching records in modern Excel, I can use:
=FILTER(B2:D100,A2:A100=G2)
This returns all matching rows.
2๏ธโฃ How would you perform a lookup using multiple conditions?
Sample Answer:
With XLOOKUP, I can combine multiple conditions.
Example:
=XLOOKUP(1,(A2:A100=G2)**(B2:B100=H2),C2:C100,"Not Found")
Here, both conditions must be TRUE.
For example:
Region = East
Product = Laptop
The formula returns the value matching both conditions.
3๏ธโฃ What is a dynamic array in Excel?
Sample Answer:
A dynamic array formula can automatically return multiple results into neighboring cells.
For example:
=FILTER(A2:C100,B2:B100="East")
The results automatically spill into the required cells.
Functions such as:
โข FILTER
โข SORT
โข UNIQUE
โข SEQUENCE
make dynamic-array analysis much easier.
4๏ธโฃ What is the #SPILL! error?
Sample Answer:
SPILL! occurs when a dynamic-array formula wants to return multiple values but the cells where the results need to appear are blocked.
For example:
=UNIQUE(A2:A100)
If another value exists in the cells where the results need to spill, Excel may return #SPILL!.
I would check and clear the cells blocking the spill range.
5๏ธโฃ How would you find the position of a value in a dataset?
Sample Answer:
I can use the MATCH function.
=MATCH("Laptop",A2:A100,0)The 0 specifies an exact match.
If I want both the position and corresponding value, I can combine MATCH with INDEX.
6๏ธโฃ Why would you use INDEX + MATCH instead of VLOOKUP?
Sample Answer:
INDEX + MATCH provides more flexibility.
For example, the lookup column doesn't need to be the first column of the lookup range, and I can return values from either direction.
Example:
=INDEX(C2:C100,MATCH(E2,A2:A100,0))
However, in modern Excel, I would generally consider XLOOKUP first because it is simpler and more flexible.
7๏ธโฃ How would you calculate a running total?
Sample Answer:
If sales are in column B, I could use:
=SUM($B$2:B2)
Then copy the formula downward.
The first reference remains fixed while the second reference expands as the formula moves down.
This produces a cumulative total.
8๏ธโฃ How would you calculate a percentage contribution to total sales?
Sample Answer:
If sales are in B2:B100:
=B2/SUM($B$2:$B$100)
Then format the result as a percentage.
The $ ensures that the total-sales range remains fixed when the formula is copied down.
9๏ธโฃ How would you compare two Excel datasets and find missing records?
Sample Answer:
I could use XLOOKUP to check whether a value from one dataset exists in another.
Example:
=IF(ISNA(XLOOKUP(A2,Sheet2!A:A,Sheet2!A:A)),"Missing","Found")
Alternatively, I could use COUNTIF:
=IF(COUNTIF(Sheet2!A:A,A2)=0,"Missing","Found")