📊 Data Analyst Interview Series — Part 6

Guys, let's continue our Data Analyst Interview Series.

Here are 10 more important Excel interview questions you should know. 👇

1️⃣ What is the difference between INDEX-MATCH and VLOOKUP?

Sample Answer:

“VLOOKUP searches for a value in the first column of a range and returns a value from another column.

INDEX-MATCH combines two functions. MATCH finds the position of a value, while INDEX returns the value from that position.

INDEX-MATCH is more flexible than traditional VLOOKUP because the lookup column doesn't have to be the first column of the selected range.”

2️⃣ What is XLOOKUP and why is it preferred over VLOOKUP?

Sample Answer:

“XLOOKUP is a modern lookup function that provides more flexibility than VLOOKUP.

It can look up values from left to right or right to left, allows a separate lookup and return range, and provides an argument for handling missing values.

For example:”

=XLOOKUP(A2,Customer_ID,Customer_Name,"Not Found")

3️⃣ What is IFERROR and when would you use it?

Sample Answer:

“IFERROR allows me to return an alternative result when a formula produces an error.

For example, if I'm calculating profit margin and revenue could be zero, I can prevent a #DIV/0! error.”

=IFERROR(Profit/Revenue,0)

“I use it carefully because hiding errors without understanding their cause can mask data-quality problems.”

4️⃣ What is the difference between CONCAT, CONCATENATE, and TEXTJOIN?

Sample Answer:

“These functions are used to combine text.

CONCAT combines text from multiple cells or ranges.

CONCATENATE is an older function that combines text values.

TEXTJOIN is more flexible because it allows me to specify a delimiter and ignore empty cells.”

Example:

=TEXTJOIN(", ",TRUE,A2:C2)

5️⃣ How would you extract the first, middle, or last part of a text value?

Sample Answer:

“I can use functions such as LEFT, RIGHT, and MID.

For example:

=LEFT(A2,5)
=RIGHT(A2,4)
=MID(A2,3,5)

The appropriate function depends on the structure of the text and the business requirement.”

6️⃣ How do you find duplicate values using a formula?

Sample Answer:

“I can use COUNTIF to determine how many times a value appears.

For example:”

=IF(COUNTIF(A:A,A2)>1,"Duplicate","Unique")

“This marks a value as Duplicate if it appears more than once in column A.”

7️⃣ What is Power Query in Excel?

Sample Answer:

“Power Query is a data preparation and transformation tool available in Excel.

It can connect to different data sources and perform repeatable transformations such as removing duplicates, changing data types, filtering rows, splitting columns, merging datasets, and appending data.

One major advantage is that once the transformation steps are created, they can be refreshed when new data arrives instead of manually repeating the entire process.”

8️⃣ What is the difference between Merge and Append in Power Query?

Sample Answer:

“Merge combines columns from two queries based on a matching key, similar to a JOIN in SQL.

Append combines rows from two or more queries with similar structures, similar to UNION ALL in SQL.

For example:

Merge → combine Customer information with Customer Transactions.

Append → combine January, February, and March sales datasets.”

9️⃣ How would you find the top 5 products by revenue in Excel?

Sample Answer:

“I would first calculate or summarize revenue by product, preferably using a PivotTable. Then I would sort the products in descending order and filter the top five.

For a dynamic solution, I could also use modern Excel functions such as SORT and TAKE.”

Example:

=TAKE(SORT(A2:B100,2,-1),5)