1. What is the default for VLOOKUP result values?
They can only look to the right. The data you want to retrieve (result values) can appear in any column to the right of the lookup values.
2. Since VLOOKUP does not handle multiple criteria natively how would you set up VLOOKUP with multiple criteria?
Add a helper column and concatenate values from columns you want to use for your criteria. Setup VLOOKUP to refer to a table that includes the helper column (should be the first column in the table.) For lookup value, join the same values in the same order to match values in the helper column. Make sure VLOOKUP is set to perform an exact match.
3. How do you set VLOOKUP for an exact match?
In the syntax for the lookup in range, column chooses FALSE to get an exact match.
4. How is VLOOKUP MATCH different from VLOOKUP or INDEX MATCH?
VLOOKUP MATCH is an improved variation of your basic VLOOKUP or INDEX MATCH formula. Using VLOOKUP MATCH allows you to perform a matrix lookup – instead of just looking up a vertical value, the MATCH portion of the formula turns your column reference into a dynamic horizontal lookup as well.
5. How are VLOOKUP MATCH and VLOOKUP HLOOKUP different?
VLOOKUP MATCH is actually very similar to VLOOKUP HLOOKUP but is slightly better because it does not require the creation of an additional row to label your column numbers.
The key difference between using VLOOKUP MATCH versus the basic VLOOKUP formula is that in addition to your vertical lookup value (what you’ll be looking up down the left side of your table) you’ll also have a column lookup value (what you’ll be looking up across the top of your column headings).
6. How to get VLOOKUP to the left?
To use VLOOKUP to perform a lookup to the left, you can use the CHOOSE function to reorder the lookup table.
7. What does HLOOKUP DO?
It is an Excel function to lookup and retrieves data from a specific row in the table. The “H” in HLOOKUP stands for “horizontal”, where lookup values appear in the first row of the table, moving horizontally to the right. HLOOKUP supports approximate and exact matching, and wildcards (* ?) for finding partial matches.
8. What does Xlookup do?
The Excel XLOOKUP function is a modern and flexible replacement for older functions like VLOOKUP, HLOOKUP, and LOOKUP. XLOOKUP supports approximate and exact matching, wildcards (* ?) for partial matches, and lookups in vertical or horizontal ranges.
9. Can OFFSET be used with other functions?
OFFSET can be used with any other function that expects to receive a reference.
10. Is using OFFSET with MATCH better?
Try and avoid using this combination if you can, because OFFSET is a Volatile Function. Volatile functions recalculate every time any cell changes. Using the OFFSET function will reduce the performance of your workbook.
11. What is a better alternative to using OFFSET MATCH?
You can achieve exactly the same results using the INDEX and MATCH combination.
12. What are some reasons to use INDEX & MATCH functions as a replacement for VLOOKUP functions?
- Right to left lookup: As any educated user knows, VLOOKUP cannot look to its left, meaning your lookup value should always reside in the leftmost column of the table. INDEX MATCH can do a left lookup with ease! The following example shows it in action: How to Vlookup a value to the left in Excel.
- Insert or delete columns safely: VLOOKUP formulas get broken or deliver incorrect results when a new column is deleted from or added to a lookup table because VLOOKUP’s syntax requires specifying the index number of the column you want to pull the data from. Naturally, when you add or delete columns, the index number changes.
- With INDEX MATCH, you specify the return column range, not an index number. As a result, you are free to insert and remove as many columns as you want without worrying about updating every associated formula.
- No limit for a lookup value’s size: When using the VLOOKUP function, the total length of your lookup criteria cannot exceed 255 characters, otherwise you will end up having the #VALUE! error. So, if your dataset contains long strings, INDEX MATCH is the only working solution.
- Higher processing speed: If your tables are relatively small, there will hardly be any significant difference in Excel performance. But if your worksheets contain hundreds or thousands of rows, and consequently hundreds or thousands of formulas, MATCH INDEX will work much faster than VLOOKUP because Excel will have to process only the lookup and return columns rather than the entire table array.
13. What is the difference between CONCAT and CONCATENATE?
The CONCAT function concatenates (joins) values supplied as references or constants. Unlike the CONCATENATE function (which CONCAT replaces), CONCAT allows you to supply a range of cells to join, in addition to individual cell references.
14. What is the difference between CONCATENATE and TEXTJOIN function?
Unlike the CONCATENATE function, TEXTJOIN lets you specify a range of cells and a delimiter to use when joining text values.
15. What does the IFS function do?
The Excel IFS function runs multiple tests and returns a value corresponding to the first TRUE result. Use the IFS function to evaluate multiple conditions without multiple nested IF statements.
16. What is the difference between IFS & SWITCH?
Like the IFS function, the SWITCH function allows you to test more than one condition without nesting multiple IF statements, making formulas with many conditions easier to read and write. One advantage of SWITCH over IFS is that the expression appears just once in the function and does not need to be repeated. However, SWITCH is limited to exact matching. It is not possible to use operators like greater than (>) or less than (<) with the standard syntax. In contrast, the IFS function actually requires expressions for each condition, so you can use logical operators as needed.
17. What can be used as an alternative to MINIFS and MAXIFS function?
The AGGREGATE function can be used to accomplish the same objective as the MAXIFS and MINIFS functions. It does not require the use of the CTRL-Shift-Enter key combination when finishing the formula. The AGGREGATE function can handle arrays natively without any fancy key combinations. Another benefit of the AGGREGATE function is its built-in ability to ignore errors in the array results. This will become especially useful in an upcoming example.
18. When are wildcards used with VLOOKUP?
Wildcards can be used when you are not sure of the exact lookup value. In order to use wildcards in Excel, you should make use of the “*” symbol.
19. What is the advantage of using OFFSET function?
There are two advantages to offset. The ability to maintain one location reference and vector to a different cell from that single reference is one advantage. This makes coding with offset more efficient for the user. The speed of vectoring is a second advantage. It is much faster to start from a known location reference and access a nearby address than it is to start at the beginning of the document and have to search through all of it to find an address.
20. What are some limitations of VLOOKUP?
The major drawback of VLOOKUP is it’s easily broken. Any shifting of columns to add new data impacts the column index number and causes the formula to need to be manually re-applied. VLOOKUP can only look to the right. In other words, 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.
21. When would you use the IF function and when would you use the nested IF function?
IF functions allow us to evaluate a situation, then act one of two ways depending on the evaluation. A simple IF is where you only need to evaluate a single criterion. A Nested IF is used when you need to evaluate several criteria in serialized order.
22. How will you find out the number of duplicate values in a range?
There might be different ways to find duplicate values from a range. One of that is, using COUNTIF function we can find duplicate values.
23. How will you create a dynamic range?
Creating a table and then using OFFSET and COUNTA functions.
24. What is the difference between COUNTIF and COUNTIFS?
The difference is that COUNTIF is designed for counting cells with a single condition in one range, whereas COUNTIFS can evaluate different criteria in the same or in different ranges.
25. Which wildcard characters can be used with COUNTIFS and what are their uses?
In Excel COUNTIFS formulas, you can use the following wildcard characters:
- Question mark(?) – matches any single character, use it to count cells starting and/or ending with certain characters.
- Asterisk(*) – matches any sequence of characters, you use it to count cells containing a specified word or a character(s) as part of the cell’s contents.
- If you want to count cells with an actual question mark or asterisk, type a tilde (~) before an asterisk or question mark.
26. Difference between SUMIF and SUMIFS function?
The SUMIF allows only one condition to be evaluated, whereas the SUMIFS (introduced in Excel 2007) allows up to 29 criteria to be evaluated. It’s the plural version of the SUMIF function.
27. How many matching modes does VLOOKUP have? What do they do?
VLOOKUP has two modes of operation: exact match and approximate match. In most cases, you’ll probably want to use VLOOKUP in exact match mode. This makes sense when you want to lookup information based on a unique key of some kind, for example, product information based on product code, or movie data based on a movie title. The approximate mode is used in cases where you’re not matching on a unique id, but rather you’re looking up the “best match” or the “best category”.
28. How will wildcards be used for partial matching in VLOOKUP?
Any time you’re using VLOOKUP in exact match mode, you have the option of using wildcards in the lookup value. It may seem counterintuitive, but wildcards let you do an exact match based on a partial match. Excel provides two wildcard characters: an asterisk (*) matches one or more characters, and a question mark (?) matches one character. For example, you can type an asterisk directly into a cell and refer to it as a lookup value with VLOOKUP.
29. VLOOKUP can only handle single criteria. How will you overcome this limitation?
One workaround is to create a helper column that concatenates values from different columns to create lookup values that behave like multiple conditions.
30. How many value result pairs can SWITCH function handle?
SWITCH can handle up to 126 value/result pairs.