Question #1
What is the syntax of the VLOOKUP function?
What are its limitations and what alternatives exist to overcome those limitations?
The VLOOKUP function is the most used lookup function in the lookup function collection. Understanding its structure and logic are paramount when performing lookup operations.
Explaining VLOOKUP in English
The VLOOKUP function takes a value you know, like a part number, and locates the value in a list. Once the known value is located in the list, an associated piece of information is returned from that record, such as the color, weight, or price of the part number.
The syntax for the VLOOKUP function is as follows:
VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])
- Lookup_value – is the know information, or the value you are trying to find in the list.
- Table_array – is the list of items with related information. The first column in the list should be a unique list if values that match the lookup_value (i.e. part numbers). This is often referred to as the “key column”. The adjacent columns in the list help form the records of related information (i.e. color, weight, price, etc…)
- Col_index_num – is the column position counting from left to right that the value to be returned is located. The first column that contains key column would be Column 1. The column directly to its right would be Column 2, and so on.
- [range_lookup] – determines whether the search will perform an exact match or an approximate match. Exact match means: if the known value is not located in the key column of the list, an error is returned. Approximate match means: if the known value is not located in the key column of the list, the closet value that is less than the known value is returned.
VLOOKUP Limitations
- VLOOKUP can only look to the right. In other words, a value to the left of the key column cannot be returned; only values in columns to the right of the key column can be seen by VLOOKUP.
- VLOOKUP will stop on the first encountered match in the key column. If duplicate items exist in the list, the first item will always be the return segment.
- Approximate match is the default posture of VLOOKUP. Although this isn’t exactly a limitation, most lookup operations require exact match logic.
- Because the col_index_num argument is typically a statically typed number, inserted or deleted columns in the table_array will cause problems.
Alternatives to VLOOKUP
The INDEX and MATCH functions, when used together, are a more powerful alternative to VLOOKUP. INDEX/MATCH does not have any of the above stated limitations.
“But if it’s so powerful, how come most people use VLOOKUP?”
Good Question!!!!
The INDEX/MATCH function combination is difficult for many users to understand. Even seasoned Excel pros sometimes struggle with this approach.
Powerful; yes. Easy to use; no.
BONUS POINTS
If you really want to impress the interviewer, mention the upcoming XLOOKUP function that Microsoft is poised to release. XLOOKUP will replace ALL existing lookup functions, have none of the stated limitations, and is far easier to use than any existing lookup function.
Question #2
How does Absolute reference differ from Relative cell reference?
When creating a formula, it will often be the case where the formula is replicated across rows or columns of similar information, such as calculating profit from all sales records.
We can write the formula once, then repeat the formula to the adjacent cells, usually by using the Fill Series handle.
Relative References
In most cases, we need the repeated formulas to make minor adjustments to their cell pointers.
When crossing rows, we need the row numbers to change.
When crossing columns, we need the column letters to change.
These cell references that change when repeated are known as Relative references.
Absolute References
When referencing a cell that will be used in each of the repeated formulas, like a tax or discount rate, we need to return to the same cell for that portion of the calculation. This is known as an Absolute Reference.
Converting Relative to Absolute
When creating a formula, after you select a cell (creating a reference that needs to remain fixed) press the F4 key. This will place dollar signs before the column and row references
D2 -> $D$2Each repeated press of the F4 key will cycle between the 4 relative/absolute combinations.
D2 -> $D$2 -> D$2 -> $D2Question #3
What is a Pivot Table and when would you use it?
When working with tables, it is often requested that aggregations of the records be created to create summary reports. As an example; instead of looking at tens of thousands or records, your boss only wants to see the total sales for each region… and there are only 5 regions.
A Pivot Table is a fast and easy way to distill those thousands of records into a 5-line summary report.
Needed Skills
If you can play computer Solitaire, you can build Pivot Tables. They’re that easy.
Each column in the data becomes a drag-n-drop field in the Pivot Table.
Creating Formulas
Pivot Tables come pre-loaded with many popular formulas for calculating sums, averages, counts, running totals, percentages, differences, etc. We can create complex formulas without ever actually writing any by hand; simply pick them from a list.
Question #4
What is Conditional Formatting?
Conditional Formatting is a tool that allows is to change the appearance of a cell based on the value placed in the cell.
For example: suppose we wish to identify and color green (the cell fill color) any sale over $100K so the sales representative will receive a bonus. If there are hundreds, thousands, or hundreds of thousands of records in the table, this will be a tedious, lengthy, and inaccurate task.
Conditional Formatting allows us to define a criterion (i.e. any sale greater than $100K) and the appearance of the cell of the criterion is met (i.e. cell fill color as light green).
This will perform the discover and coloration in a tiny fraction of the time, not miss or miscolor any cells, and continually monitor the cells for changes in the data.
If a sale that did not previously qualify for the coloring, but is changed to a value that now qualifies, the cell color will automatically change.
The reverse is also true.
Features
Conditional Formatting is not limited to cell and font colors. We can implement icons, data bars, and color scales to visually identify data.
Question #5
When would you use the IF function or a Nested IF function?
IF functions allow us to evaluate a situation, then act one of two ways depending on the evaluation.
Simple IF Function
A simple IF is where you only need evaluate a single criterion.
Example: If a sale is over $100, the shipping is free. Otherwise, a $10 shipping charge is calculated.
=IF(A1 > 100, 0, 10)Nested IF Function
A Nested IF is used when you need to evaluate several criteria in serialized order.
Example: If the employee has worked for the company more than 10 years, they receive 4 weeks vacation. If the employee has worked for the company more than 7 years, they receive 3 weeks vacation. If the employee has worked for the company more than 4 years, they receive 2 weeks vacation. Anyone else receives 1 week vacation.
=IF(A1 > 10 years, 4 weeks, IF(A1 > 7 years, 3 weeks, IF(A1 > 4 years, 2 weeks, 1 week)))(NOTE: The above formula example is not accurate. It was embellished with English to make it more understandable.)
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
