Excel

What is the relative reference operator and what does it do?

$: fixes the cell so that the reference stays the same when copied

What is the union operator?

,

How does the array need to be sorted when using a match_type value of -1 or 1 in the MATCH function?

-1: descending order 1: ascending order

What are the the match_types of the MATCH function?

0: exact match -1: greater than or equal to 1: less than or equal to

What is the difference between using MATCH("item", E1:E10, 1) and MATCH("item", E1:E10, -1)?

1 requires an ascending sorted range and finds the largest value less than or equal to "item"; -1 requires a descending sorted range and finds the smallest value greater than or equal to "item".

If A1 has 5 and A2 has 10 and you use the formula =$A1 + 3 in B1, what will B2 contain after copying down?

13

If C1:C4 contains {100, 200, 300, 400}, what will =MATCH(250, C1:C4, 1) return?

2

What does the formula =INDEX(A1:B3, 2, 1) return if A1 contains 1, A2 2, A3 3, B1 4, B2 5, B3 6?

2

What is the syntax to reference rows 2 to 5 in Excel?

2:5

If A1:A5 contains the values {10, 20, 30, 40, 50}, what does the formula =MATCH(30, A1:A5, 0) return?

3

If D1:D5 contains the text values {"Red", "Blue", "Green", "Yellow", "Orange"}, what will the formula =MATCH("Green", D1:D5, 0) return?

3

If H1:H5 contains {5, 10, 15, 20, 25} and you use the formula =MATCH(18, H1:H5, 1), what is the result?

3

What will =INDEX(A1:B3, 1, 2) return in the same context?

4

If D1 contains =A1 + B1 and A1 changes to 3 and B1 to 5, what will D1 return?

8

What will the formula =$C$3 + 5 become when copied to cell D4?

=$C$3 + 5

Write a formula using a mixed reference to always refer to column C while allowing the row to change.

=$C1

What will the formula =A$1 + B2 become in cell C2 when copied to D3?

=B$1 + C3

What will the formula =B1 + 10 become in cell B2 when copied down?

=B2 + 10

In a range A1:C3, what is the formula to get the value from the second row and third column?

=INDEX(A1:C3, 2, 3)

How would you use the INDEX function to retrieve a value from the third row of column B?

=INDEX(B1:B3, 3)

If you have a range named "Scores" from A1 to A10, how would you use INDEX to get the value in the fifth position?

=INDEX(Scores, 5)

Write a formula using LARGE to find the 2nd largest test score in the range A1:A10.

=LARGE(A1:A10, 2)

If A1contains scores, what formula would you use to find the top 3 scores?

=LARGE(A1:A5, {1, 2, 3})

Write a formula using LARGE to find the largest number in the range B1:B5.

=LARGE(B1:B5, 1)

Write a formula using LARGE to find the 3rd largest value from the combined ranges A1:A3 (10, 20, 30) and C1:C2 (40, 50).

=LARGE({A1:A3, C1:C2}, 3)

Write a formula using MATCH to find the position of the value "Banana" in the range B1:B10.

=MATCH("Banana", B1:B10, 0)

Write the OFFSET formula to return a 2-row by 3-column range starting from B2.

=OFFSET(A1, 1, 1, 2, 3)

Write a formula that references the cell 2 rows down and 1 column to the right from A1 (which would be B3), and returns the value in that cell.

=OFFSET(A1, 2, 1)

Write a formula using OFFSET to return the value in C5 (3 columns to the right of A1).

=OFFSET(A1, 4, 2)

Write an OFFSET formula that dynamically references the cell based on C1 (which points to A2) and returns the value from C3 (1 row below A2 and 2 columns to the right).

=OFFSET(INDIRECT(C1), 1, 2)

How can you sort the range A1:A10 in descending order?

=SORT(A1:A10, 1, -1)

Write a formula to sort the array {3, 1, 2}.

=SORT({3, 1, 2})

Write a formula to sort by multiple columns (A and B) where A is sorted ascending and B descending.

=SORTBY(A1:B10, A1:A10, 1, B1:B10, -1)

Use a formula to sum the range A1 while excluding A5.

=SUM(A1:A4) + SUM(A6:A10)

Write a formula to sum the values in the range A3:A6 using OFFSET and no manual reference to the cells.

=SUM(OFFSET(A1, 2, 0, 4, 1))

You have the following employee names and their salaries: Jane Doe: $45,000 John Smith: $50,000 Alice Brown: $55,000 Bob White: $60,000 You want to find the salary of John Smith using VLOOKUP.

=VLOOKUP("John Smith", A2:B5, 2, FALSE), 50,000

You have the following students and their scores: Sally: 88 Tommy: 92 Jessica: 85 Mike: 76 You want to find the score for Tommy using VLOOKUP.

=VLOOKUP("Tommy", A2:B5, 2, FALSE), 92

You have a table that shows commission percentages based on units sold: 0 units: 5% 10,000 units: 7% 20,000 units: 10% 30,000 units: 12% You want to find the commission percentage for 15,000 units using VLOOKUP.

=VLOOKUP(15000, A2:B5, 2, TRUE), 7%

You have customer IDs, customer names, and order amounts: Customer ID 1001: Sarah, $300 Customer ID 1002: Tom, $500 Customer ID 1003: Amy, $700 You want to find the customer name for the order amount $500 using VLOOKUP.

=VLOOKUP(500, C2:A4, 2, FALSE), Tom

You have a list of product IDs, product names, and prices: Product ID 101: Widget, $10 Product ID 102: Gadget, $15 Product ID 103: Gizmo, $20 You want to find the price for the product ID entered in cell D2, where the value in cell D2 is 102.

=VLOOKUP(D2, A2:C4, 3, FALSE), 15

Write a reference for the non-contiguous ranges A1 to A3 and C1 to C3

A1:A3, C1:C3

What is the range for the first three rows and the first two columns?

A1:B3

What types of forms can be used with the INDEX function and what is their use case?

Array: used for contiguous, simple arrays. Reference: used for contiguous arrays. Useful for conditional statements.

How do you reference all cells in column C?

C:C

What does the AND function do?

Checks if all conditions are true.

What does the OR function do?

Checks if at least one condition is true.

What does the VLOOKUP function do?

Finds a searched value in the left column of the passed array and returns the corresponding value in a specified column.

Is the MATCH function case sensitive when matching strings?

No (Apple = apple)

What does the OFFSET function do?

Returns a reference to a range that is a certain distance from the provided starting point.

What does the IFERROR function do?

Returns a specific value if an error is thrown from the provided function.

What does the IF function do?

Returns a value if a condition is true and another if a condition is false.

What does the MATCH function do?

Returns the index of the first match to the match_value argument.

What does the LARGE function do?

Returns the kth largest value in an array.

What does the INDEX function do?

Returns the value of the index provided.

What does the NOT function do?

Reverse logic of the IF function

What is the 3D reference for cell A1 across sheets Sheet1 to Sheet3?

Sheet1:Sheet3!A1

What does the IFNA function do?

Similar to the IFERROR function but specifically designed to handle #N/A

What does the SORTBY function do?

Sorts by multiple columns/rows.

What does the SORT function do?

Sorts the array by a single column/row.

What are the arguments for the LARGE function?

array: the array k: the kth largest value to return

What are the arguments for the INDEX function in array form?

array: the array to search through. row_num: the row to pull a value from (0 to pull from all). column_num: if the array contains multiple columns, which column to pull the value from (default 1, 0 to pull from all).

What are the arguments for the SORTBY function?

array: the array to sort. by_array1: the first array to sort by. sort_order1: the order to sort in (default 1 for ascending, -1 for descending). ...

What are the arguments for the SORT function?

array: the array to sort. sort_index: the column/row to sort by (default 1). sort_order: the order to sort in (default 1 for ascending, -1 for descending). by_col: specify sort by columns (TRUE) or rows (default FALSE)

How do you select everything?

ctrl + shift + space

How do you select an entire column?

ctrl + space

What are the arguments for the AND function?

logical1: the first condition to check ...

What are the arguments for the OR function?

logical1: the first condition to check ...

What are the arguments for the NOT function?

logical: the condition to test

What are the arguments for the IF function?

logical_test: the condition to test value_if_true: the value to return if true value_if_false: the value to return in false

What are the arguments for the VLOOKUP function?

lookup_value: the value to search for table_array: the array col_index_num: the column to pull the corresponding value from range_lookup: TRUE finds an approximate match (must be sorted in ascending order), FALSE finds an exact match (default)

What are the arguments of the MATCH function.

match_value, array_index, match_type

What are the arguments of the OFFSET function?

reference: the starting point rows: how many rows away from the starting point cols: how many cols away from the starting point height: the height of the reference width: the width of the reference

What are the arguments for the INDEX function in reference form?

reference: tuple containing all arrays to reference. row_num: the row to pull a value from (0 to pull from all). column_num: if the array contains multiple columns, which column to pull the value from (default 1, 0 to pull from all). area_num: the array to pull a value from (default 1).

How do you create a 3D range?

sheet1:sheet2!range

How do you select an entire row?

shift + space

What is the intersection operator?

space

What are the arguments for the IFERROR function?

value: the function to evaluate value_if_error: the value to return if an error is thrown

What are the arguments for the IFNA function?

value: the function to evaluate value_if_na: the value to return if NA is thrown