As part of interview process a lot of companies test candidate's knowledge of Microsoft Excel by asking them either to complete Excel Test or work with the real data in application, to complete certain assignments. And which Excel interviewer can miss questions related to VLOOKUP formula. In this post we will look at typical excel VLOOKUP questions presented to interview candidate and how to answer them.
Typically employers use workbooks with large sets of data (100K+ rows or more) which wouldn't be perfectly formatted to simulate real working conditions. In this tutorial we will look how you can this interview question and assessment test questions and I will teach you how you can succeed on such an assignment. We will look at the most frequent version of the question and we will also look at the different variation of the question being asked during the interview. At the end of the post you can watch video tutorial with all the answers and if you would like to download source files to follow along with the video.
VLOOKUP is considered one of the advanced features in Microsoft excel and understanding of VLOOKUP is required for a lot of different jobs especially if candidate will be working on the project. People working on the project are typically expected to offer advice and expertise to organisations to help them improve their business performance in terms of operations, profitability, management, structure and strategy they heaving use data analytics tools and knowledge of Microsoft Excel is successful to be successful on the job. These people could be considered consultants to the organization and typically consultants work on assignments or projects with specific deadline and provide advice to the clients based on the data they have analyzed
Most of the time resources working on the projects would perform below listed activities:
- Analyze manufacturing, marketing and/or sales data
- Conduct research, surveys and interviews to gain understanding of the business
- Detect issues and investigating ways to resolve them and assess the pros and cons of possible strategies, based on the data they have
- Based on the data analyzed consultant would make recommendation for improvement, using computer models to test them and presenting findings to client
A lot of times these people are involved in implementing solutions by installing and configuring IT systems or developing and implementing new procedures or training. To complete accurate analysis and provide recommendations to the clients knowledge of Microsoft Excel is essential for consulting job and specifically knowledge of how to effectively use VLOOKUP formula to perform all of the above listed tasks.
In this tutorial you will learn VLOOKUP formula and review sample VLOOKUP Excel Assessment Test and Interview questions. You will also learn typical implementations of VLOOKUP and we will look at Advanced VLOOKUP exercises to get you ready for Excel Interview. You will be able to download Excel Source Files used in the tutorial.
VLOOKUP Formula In Excel
Why Employers Test Job Candidates for VLOOKUP knowledge?
VLOOKUP is one of the most advanced formulas in Excel and questions related to VLOOKUP are almost guaranteed on any exam. Typically VLOOKUP questions are asked as part of Advanced Microsoft Excel Skills Test because VLOOKUP is heaviligy used to do data lookup, manipulation and to support business and financial calculations in Microsoft Excel.
Since Microsoft Excel is a complex business application which allows companies to record, track, and analyze data and perform very complex modelling and calculations, companies would like to make sure that candidate has advanced skills to be successful on teh job. Since Microsoft Excel is used in a lot of organizations, employers would like to make sure that candidates has solid Excel skills and experience using the tool. A new employee who can comfortably navigate is prepared to immediately contribute by producing meaningful, data-driven spreadsheets, reports, and graphs to best serve company needs. And knowledge of VLOOKUP is very important in your success on the job.
How Employers Test Candidates?
There are multiple providers that conduct the tests and each one of the tests is a little different. Most of the time employers use below providers to conduct the tests
Because Microsoft Excel is so popular and useful, companies are trying to evaluate candidates by conducting pre-employment assessment test. An Excel Interview Test is a screening process employers use as part of the candidate employment interview to test a potential candidate on their knowledge and proficiency of Microsoft Excel.
Each employer might use a slightly different variation of the Excel test. But regardless of test there are typically multiple questions, related to VLOOKUP formula.
The Excel Assessment Test typically broken down into a multiple-choice section and an interactive portion. Some tests are timed but some of them do not have a limit on how long applicant can take to answer the questions, but there might be exceptions.
There are different types of questions typically being presented during Excel Test and VLOOKUP questions can be part of any of the sections below :
- Multiple Choice Questions
- Multiple Answer Questions
- True/False Questions
- and Interactive Questions
It is rare that only one type of question is used during the test – most of the time questions are part of the large pool and testing software randomly selects certain number of questions from the pool and presents them to the candidate.
Now lets look at VLOOKUP Formula and learn how it is typically used in Microsoft Excel.
VLOOKUP stands for “Vertical Lookup” and what that means is we are looking for results in the columns and there is also a sister function to VLOOKUP called HLOOKUP where H stands for horizontal and that is for getting values stored in the rows. We are going to focus on VLOOKUP and trust us once you understand how VLOOKUP works then the HLOOKUP will be much easier to understand.
In more technical terms VLOOKUP looks up using a unique identifier and brings you result associated with the lookup value.
The VLOOKUP function is available across almost every spreadsheet platform including all versions of Excel, Excel Online, Google Sheets, Apple Numbers, OpenOffice and LibreOffice etc.
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
There are four parts to VLOOKUP formula which make everything tick. Once you understand each of it, the concept will be easy to grasp. Let’s try and understand it in simple everyday terms, say finding a particular shirt in a wardrobe and suppose there are two sections to the wardrobe.
Okay so in those terms, the shirt we are looking for will be represented by lookup_value and “table_array” will represent the wardrobe and col_index_num will be representing one of the two sections of the wardrobe and finally the [range_lookup] is an optional parameter which will tell us whether we are looking exclusively for a particular shirt or we could be a little lose with the required demand.
So now you understand somewhat of what we are trying to do with this formula let’s try and understand each part of the syntax using the formula and real examples.
This part of the formula defines the value we are trying to search for and this could either be a value which could either be a value (number, text) and or cell reference (reference to a cell containing the lookup value).
Let’s have a look at some examples and how this works.
- In the first example we are going to look up using text “Green” and get the shirt number associated with the color Green, the formula for this in the case as shown in the figure below is VLOOKUP("Green",A3:B7,2,FALSE). As you can see for looking up text, we have to put the text between two quotation marks. Have a look at the example below to see the formula in action.
- In the second example we are going to look up a number using the VLOOKUP formula and it will be pretty much the same except we are going remove quotation marks from the equation, have a look at the figure below and see how we are doing that. The formula used in the figure below is VLOOKUP(10,A3:B7,2,FALSE)
- In the third example we are going to reference a cell and get the lookup value from the cell and get the results from our VLOOKUP function and in the below example I going to reference the cell D2 and get the results by looking up value from there and the benefit of doing this is you can lookup many values without having to change formula every time. So, the formula for looking up using the cell D2 would look like VLOOKUP(D2,A3:B7,2,FALSE). Have a look at the example below to understand it better.
This section defines usually two or more columns of data the VLOOKUP function looks for value. The VLOOKUP function always looks for identifier in the first column so from above see the identifier was first the color and on next example, the identifier is the “Shirt #” and in both the figures you can see the lookup column is the first one.
The Lookup values are case insensitive so you can enter values without worrying about whether its upper case or lower case.
Have a look at the figure above the formula used in it, is VLOOKUP("Green",A3:B7,2,FALSE) and you can see the colors are listed in column are A3:A7 and that is the starting column of the array and same with Fig 2 VLOOKUP(10,A3:B7,2,FALSE) you can see the lookup value “Shirt #” is again stored in column in section A3:A7 which again is in the start of the table_array.
This part of formula defines the column from which we want to get the results from as explained above the left-most column in a VLOOKUP array is always reserved for the identifier and so if we are looking for any value in the column right next to the identifier then the column index number would be 2 as the first column is our identifier and the next column is our results that we want to extract.
Have a look at the examples shown above to see that in all the instances above the column number is 2 and that is because we are extracting the second column value.
Have a look at the figure 4 to see a different example of the same. In the following example the formula used is VLOOKUP(E2,A3:C7,3,FALSE) and as you can see as a result we are getting the results from the column C.
The fourth and final piece of the puzzle of a VLOOKUP formula is either TRUE or FALSE and while optional as per Excel it should rarely be considered optional as the formula defaults to TRUE when left unfilled.
So, the difference between TRUE and FALSE is simple whenever we are looking for exact results, we should always use FALSE as we have been doing in all of our examples above and TRUE range lookup is useful for finding approximate results.
So how does approximate results work, essentially it matches the closes lowest value with lookup value and so for best results its always advised to use it on data sorted by ascending order.
Let’s have a look at couple of examples of approximate match to further drive our point home
VLOOKUP Example 1
The first example shows the approximate match of 250 and Excel finds the lowest close value to the lookup value which is 120 and note while 250 will be much closer to 300 but it finds the lowest closest value to 120 it is and gets us the result from the second column.
Have a look at the figure below to see the formula in action.
VLOOKUP Example 2
In this example we are looking up for the value 60 and you can notice it in the figure below, Excel finds us the results against the 50 value. Which again follows the same rule, that is finding the lowest closest value.
Now that we have figured out how this formula works and what elements make it tick, now let’s figure how you can use it and how can it benefit you in the long run.
You can do a lot with VLOOKUP and that includes looking up data in the same sheet, looking up data in the different worksheets, looking up data in different workbooks.
We have already seen VLOOKUP in action in the same sheet and so now let’s look at some other examples and explore more on what you can do.
Let’s have a look at different scenarios using real examples.
It’s very rare sight to see VLOOKUP done on the same sheet, as its usually done to find correlating data across the sheets and in order to do that we have to reference the table_array argument using the sheet name followed by exclamation mark and occasionally surround the sheet name by single quotation marks depending on if the sheet name has space in its name.
Alternatively you can simply type the lookup_value and then switch the sheet and navigate to the data where it is and select the entire array area and it will populate the array range on the formula with the correct format and I recommend this method as opposed to typing as it will help you save a ton of time and problem solving with the format.
With that said let’s look at a couple of real examples.
In the first example below you can see we are referencing the table_array part of the formula from the “sheet5” and as you can see it follows the naming convention we explained above the “sheet5” is the sheet name followed by the exclamation mark followed by the range of the table_array.
Now in the second example I am going to show you how the single quotation marks play their part in the naming of the sheet.
As you can see below in, I used the same example I just changed the sheet name to “Sheet Name” and you can see how the sheet name is now wrapped around a single quotation mark and that is how Excel deals with the space in the names.
This is another spectacular way you can use VLOOKUP to solve any of your problems. Suppose you want get information from a different workbook using a common identifier and so if you have the workbook open then all you need to do is have the workbook name be surrounded by square brackets.
So, suppose the sheet name is “Sheet Name” and workbook name is “tutorial” then your formula would look like something like this =VLOOKUP(C3,'[tutorial.xlsx]Sheet Name'!A3:B14,2,FALSE).
Now as you can see, we already utilized the spacing in this example and as a result the sheet and workbook name is surrounded by single quotation marks.
Now once you close your workbook with the lookup_array then your formula will now show the full path of the workbook have a look below in the example to see it in action.
One big tip I would always suggest going for is select the range by navigating to workbook and sheet, instead of typing it as it will automatically fill up the workbook, sheet and range name without you have to do any typing at all.
Like a lot of things in Excel, you can refer to named ranges in the lookup_array segment to make your formulas easier to write and more dynamic than say a static range.
Using named range is pretty simple in the VLOOKUP for example we define a named range after selecting a range and creating a range name by simply typing it in there. Named Ranges can’t have spaces in its name and we will go in more detail about the same some other time
Have a look below in order to see us creating a named range then after that we will create a VLOOKUP formula using that.
As you can see below, we have used the named range we created above in the below formula in lookup_array segment successfully.
The formula used below is =VLOOKUP(E2,Wardrobe,3,FALSE) which does not refer directly to a range but will work just as the same.
Some things to note while using VLOOKUP.
- VLOOKUP function always looks at right and the identifier has to be the very first column in the table_array for it to work.
- VLOOKUP is case insensitive so BuS is same as bUS.
- Always use absolute values while defining ranges i.e. use ranges with dollar signs to fix the table_array range as that would not be a problem when dragging the formula. So, for example in place of A3:B7 use $A$3:$B$7 as that fixes the range.
- Also, in most cases you will be using “False” parameter so try to stick to that unless on places where you want approximate matches which are rare.
- About the errors
- #N/A error occurs when VLOOKUP can’t find a match.
- #VALUE! error occurs when the col_index_num is less than 1.
- In case the col_index_num is higher than the number supplied in the array then the #REF! error is reflected in the formula.
VLOOKUP Assessment Test Questions
What would the correct formula for the looking up the value of shirt # corresponding to the color Yellow based on the following example data?
What would be the correct formula for looking up number 30 and give us the color corresponding to the number based on the references as per the following image.
What would be the correct formula for referencing another cell and get a VLOOKUP results from the column that contains pant # based on the cell E2 and the array shown in the image below?
For finding best results in approximate match we have to sort the data by which order?
The correct answer is .
VLOOKUP can only look for value on which side of identifier column
- Left but with some alteration to the formula
- Not Applicable
Which is the correct VLOOKUP formula for dataset shown on the below image which resides on sheet “DataSheet1” and we are referencing from the sheet Question 6 for finding the Shirt # based on the color “Cyan”.
- =VLOOKUP("CYAN", DataSheet1!A3:C7,2,FALSE)
- =VLOOKUP(Cyan, ‘DataSheet1!A3:C7,2,FALSE)
- =VLOOKUP(“Cyan”, DataSheet1!A3:C7,2,FALSE)
- =VLOOKUP(CYAN, DataSheet1!A3:C7,2,FALSE)
- If you can navigate between questions go through the entire test and do the easiest, shortest questions first. Save the more difficult questions to come back to later; perhaps another part of the test will help you out with that question or trigger knowledge that you didn’t think you had and you already answered the questions you know the answers for.
- Keep a positive attitude throughout the test and stay relaxed. People who are positive and relaxed do better on tests than those who are negative and stressed out.
- Consider using flashcards to help you study. You can purchase blank flashcards or create them yourself from paper and put your questions and answers. Create flashcards for the questions you have missed in the past, so you can refresh the knowledge.
- Understand how much time you have to complete the test by dividing number of question in the test to the allotted time to determine time per question average you should use. Use this calculated average as a baseline and try to limit the time you spend on each question.
- Always watch for time remaining and be prepared to submit test results, before time expires.
- Even if you have no idea about the answer you still should try to guess and answer the question, since your guess might be correct
Next Step: Download PDF EBook
Top 50 Excel Assessment Test Questions and Answers
Top 50 Excel Assessment Test Questions and Answers PDF EBook teaches you everything you need to get ready for Basic and Intermediate Microsoft Excel Interview and prepare for Excel Assessment Test. You will practice most frequently asked questions on the exam and will learn what to do when you arrive at the exam testing center, how to manage your time when taking the exam, tips to manage your time during assessment test, tricks for answer questions in different formats 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 to help them get employed. Throughout his consulting career Vadim studied a lot of tools and technologies and learned hiring process by helping organizations to hire candidates and also supported students to get prepared and to pass interview and assessment tests.
Vadim loves to share his knowledge with and enjoys teaching new skills to help you get hired for your dream job.
Download XLSX Files Used in This Tutorial
There are 2 Excel XLSX work files included in this free download. Complete the form below and you will be redirected to the download page.
- Advanced.Excel.Interview.Questions.and.Answers.XLSX - XLSX Source File
- Advanced.Excel.Interview.Questions.and.Answers.ZIP - XLSX Source file in Zip Archive