Nick's guide to Excel / VBA Interview Questions

Nick's picture

I have hosted hundreds of Excel / VBA interviews, and I can work out very quickly how good someone is.

The biggest blunder to make in an interview is to say something like: "Oh, that's easy, I can do that in 5 minutes with the compiler and help files"

Well, in my interview, you have no compiler and no help files, so don't ignore syntax, or rely too much on help files.

You wouldn't turn up to a French interview with a dictionary and translate every word now would you ?

; - >

Here are some Excel VBA interview questions I might ask you.. Add a comment if you think you have a good answer.

Nick's picture

7. Volatile functions

What's a Volatile function ?

Functions that will trigger

Functions that will trigger recalculations every time a calculation is performed, e.g. F9.

Nick's picture

6. VLOOKUP vs INDEX MATCH

What's the difference between using VLOOKUP vs INDEX + MATCH ?

difference between using VLOOKUP vs INDEX + MATCH

Vlookup have a limitation of searching, that it only search in right side, of the searching data,

 

Example

Emp table

Emp_Code   Emp_name   Emp_ID

011                abc              121

012                adef           131

Result

Emp_Name     Emp_Code

abc                vlookup(not able to do that search)

adef               index&match function can do that type of searching

 

 

VLOOKUP vs INDEX MATCH

VLOOKUP works correct only with sorted values... and can lookup in the 1st column only..
Index + Match is best for getting values from any part of the array....

Vlookup must always return a

Vlookup must always return a value to the right of the lookup column. Using the Index and Match functions together, you can return a value to the left of the lookup column.

Vlookup will allow you to

Vlookup will allow you to search for an item in the 1st column.
The other will allow the seach param on any column

Nick's picture

5. Excel Function

You have the following data: Data

Write a function to go in cell C3 that will return the house name that corresponds to the number typed in C2

Ans 5

=IF(B2="","", VLOOKUP(B2,E2:F8,2,0))

Solution

No need of using if function for this.

Formula is =VLOOKUP(C2,E2:H8,2,FALSE)

Thats it, it returs the value searching for is Building name