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?
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
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 + ↓
This helps to navigate to the end of the data
- Wild Card Match in Excel
There are also other types of Wild Card match available - see below image
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
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.
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 =VLOOKUP("*" & F3 & "*",A6:H706,8,FALSE)
Then, we get 3165.
If we search for Unit sold 3165, we see that Transaction ID value contains 0330.
[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
