Advanced Excel Tutorialsconsulting interview questionsExcel Assessment TestExcel Formulas and FunctionsExcel Interview QuestionsExcel Test for Interview Candidatesexcel test questions and answers pdfVideo TutorialVLOOKUP in Excel

Consulting Interview Questions and Answers: Advanced Excel VLOOKUP

Excel Consulting Interview VLOOKUP Questions And Answers

A lot of time consulting companies test candidates by asking them to work with the real data in Microsoft Excel Application, instead of using test format or simulator questions. Companies might also share with candidates workbooks with large sets of data 100K_ rows which wouldn't be perfectly formatted to simulate real working conditions. In this post we will look at one of these interview and assessment test questions and I will you can leaarn how you can succeed on such an assignment

In this tutorial we will look at the VLOOKUP Excel Assessment Test Question asked as part of the consulting interview. A lot of time consulting companies test candidates based on the real assignments that they had to complete and use Microsoft Excel Application, instead of using test format or simulator questions. Companies might also share with you workbooks with large sets of data 100K_ rows which wouldn't be perfectly formatted to simulate real working conditions.

Question. Client is looking for a number of items that have been sold in the set of data. Client only remembers that Transaction ID contained the value 0330, somewhere in transaction ID field

How can you help client to find number of items sold for this transaction id using VLOOKUP formula?

We will also look at how to solve Variation of this Question: Client only remembers that Transaction ID contained the value from cell F3, somewhere in transaction ID. How can you help client to find number of items sold for this transaction id using VLOOKUP formula?

Excel Consulting Interview - Advanced Excel VLOOKUP Test Questions and Answers

 

VLOOKUP Overview

VLOOKUP function helps you find items in a table or range by role. It has four parameters which are:

  • Lookup value: The value of what you're looking for.
  • Excel range: Where the value you're looking for is located.
  • Column number or index number in the range containing the return value
  • True or false

True is for approximate match and false is for exact match.

VLOOKUP Example

VLOOKUP

Business Problem: Need to find matching clothes: find Pants ID that match color of the Shirt.

In this example, we have a wardrobe which has shirts and pants. Each shirt has a unique ID and each pant has a unique ID. For example, for green, we have shirt ID number 10 and pants ID number 33, same for blue, yellow, red and cyan. We will store the matching color in the cell F7. The color and the match that we are looking for is green.

  • Input the formula =VLOOKUP(F7,B8:D12,3,FALSE) in the cell B15.

That is, get the value we are looking for from F7 which is green. Then look in the range B8 through D12. Return the value from index 3. Shirt color is index 1, shirt number is 2 and pant number is 3. Then use an exact match which is value false and the return value will be 33. So, for green, matching pants would be 33.

Important things to note:

  • Ctrl + ↓

ctrl + down arrow

This helps to navigate to the end of the data

  • Wild Card Match in Excel

Wild Card Match in VLOOKUP

 

There are also other types of Wild Card match available - see below image

Wild card match in Excel

 

We will be using wild card match with "*". Wild card allows us to find values using * to represent any character or any numbers of characters

VLOOKUP formula

Business Problem: Client is looking for a number of items that have been sold in the set of data. Client only remembers that Transaction ID contained the value 0330, somewhere in transaction ID field. How can you help client to find number of items sold for this transaction ID using VLOOKUP formula?

Analysis of the data:

  • Transaction ID field would be our VLOOKUP column where we will be looking up for that value in the field.
  • The column that represents items that have been sold in this set of data is column H i.e. Units Sold.
  • Client is looking for units sold based on the transaction ID. So, our VLOOKUP value would be transaction ID, but item that we'll be looking for would be H.
  • To get to the end of the data, use keyboard shortcut; ctrl + ↓. In this example, that brings us to row 706.
  • Input the formula =VLOOKUP("*0330*",A6:H706,8,FALSE)
  • Type * in the beginning and the end of 0330 i.e. the value we know.
  • We're looking in the range A6 through H706. Sometimes, consulting companies provide you with the huge number of data, so it's very important to know the end of your range.
  • We’re looking in index number 8 which is column H.
  • We're looking for exact match which is represented by the value false.

Then, we get 3165.

VLOOKUP formula excel

If we search for Unit sold 3165, we see that Transaction ID value contains 0330.

Question Variation: Client only remembers that Transaction ID contained the value from cell F3 which is 0330, somewhere in transaction ID.

Input the formula

  • Input the formula =VLOOKUP("*" & F3 & "*",A6:H706,8,FALSE)

Then, we get 3165.

Input the formula result

If we search for Unit sold 3165, we see that Transaction ID value contains 0330.

Video Tutorial

[PDF Download] Advanced Excel Questions and Answers PDF EBook

Topics Covered in this EBook

  • Advanced Excel Formulas and Function Questions
    • VLOOKUP Questions and Answers
    • LOOKUP
    • SUMIF, SUMIFS
    • HLOOKUP, LEFT, FIND, SEARCH, SUBSTITUTE
    • COUNT, COUNTA, COUNTIF, COUNTIFS
  • Pivot Table Questions
    • Pivot Table setup and configuration
    • Dynamic ranges in Pivot Tables
    • Data Sorting and grouping in Pivot table
  • Excel Keyboard Shortcuts Questions
    • Most frequently used Excel Shortcuts
  • Strategies to Successfully Pass Excel Interview & Assessment Test
    • Preparation for the Exam. What do you need to know
    • Preparation for the day of test
    • Tips to manage your time during Assessment Test
    • Tricks for Answering Questions
      • Strategies to answer Multiple Choice Questions
      • Tips and Tricks to answer True/False Questions
      • How to succeed with Multiple Answer Questions
      • Simulator questions: everything you need to know

and much much more

 

 

About the Author: Vadim Mikhailenko, MBA

Vadim lives in Milwaukee, Wisconsin and works as an Information Technology Consultant, Coach and Educator. Vadim is very passionate about Milwaukee community and teaches classes to underprivileged students at local community college. Throughout his consulting career Vadim had to study a lot of tools and technologies and learned hiring process by helping organizations to hire candidates and supported students to get prepared and pass interview and assessment tests.

Vadim loves to share my knowledge with others and enjoys teaching people new skills to help them get hired for the dream job.

Download Files Used in This Tutorial

There are 2 Excel Assessment work files included in this free download. Complete the  form below and you will be redirected to the download page.

  • Consulting Interview Preparation - Advanced Excel VLOOKUP Test.xlsx - XLSX Source File
  • Consulting Interview Preparation - Advanced Excel VLOOKUP Test.xlsx.zip - XLSX Source file in Zip Archive