Excel Functions Quiz

Which of the following best describes the purpose of the VLOOKUP function in Excel?

• It looks up a value in a row and returns a corresponding value from the same row.

• It looks up a value in the leftmost column of a table array and returns a corresponding value from a specified column.

• It returns the position of a value in a given array.

• It returns the intersection of a particular row and column in a given table.

When using VLOOKUP, why might you set the final parameter (range_lookup) to FALSE?

• To allow approximate matching.

• To allow any matching value in the lookup column.

• To require an exact match rather than an approximate match.

• To ignore blank rows in the dataset.

Consider the formula: =VLOOKUP($A$2, Sheet2!$A$1:$C$100, 3, TRUE) Which of the following statements is correct if the lookup value is not exactly found?

• The formula will return a #N/A error.

• The formula will display "0".

• The formula will return the next largest value that is less than the lookup value.

• The formula will return the next smallest value that is greater than the lookup value.

Which of the following statements about HLOOKUP is incorrect?

• It looks up values in the top row of a table array.

• It can be used with both approximate and exact matches.

• It uses row index numbers to retrieve corresponding values from below the match.

• It searches for a match in the leftmost column and returns data from a specified column.

Which function can be considered a more powerful and flexible alternative to VLOOKUP and HLOOKUP in newer versions of Excel?

• INDEX

• MATCH

• XLOOKUP

• LOOKUP

In the formula: =INDEX(A1:C10, 4, 2) What value is returned?

• The value located in the 4th column and 2nd row within A1:C10.

• The value located in the 4th row and 2nd column within A1:C10.

• The entire 4th row from columns A to C.

• The entire 2nd column from rows 1 to 10.

You have the formula: =INDEX(A2:A6, MATCH("Mango", B2:B6, 0)) What is this formula designed to do?

• Return the row number of the cell containing "Mango" in range A2:A6.

• Return the position of "Mango" in range B2:B6.

• Find "Mango" in B2:B6 and return the corresponding value from A2:A6 in the same row.

• Return the value "Mango" if found in B2:B6.

Which of the following is a primary limitation of the LOOKUP function compared to VLOOKUP and HLOOKUP?

• It cannot handle text lookup values.

• It only supports exact matches.

• It requires the lookup range to be sorted in ascending order for correct results.

• It cannot return data from non-adjacent ranges.

Which best describes how the MATCH function works?

• It returns the data in a table based on a specific row and column reference.

• It compares two separate arrays and outputs differences.

• It returns the position of an item in an array that matches a specified value.

• It always returns the first cell in a given range that has any content.

If you want to look up a value in a table regardless of whether the desired column is to the left or right of the lookup column, which combination of functions can you use in older Excel versions (pre-XLOOKUP) to achieve this?

• VLOOKUP alone

• HLOOKUP alone

• INDEX and MATCH

• LOOKUP alone