Grade 9 - Lesson 4 : Excel - Index and Match

If you have a well-structured table, with information arranged vertically, and a column on the left which you can use to match a row, you can probably use this function.

• HLOOKUP

• MATCH

• INDEX

• VLOOKUP

Choose the correct syntax of VLOOKUP

• VLOOKUP(table_array, lookup_value, col_index_num, range_lookup)

• VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)

• VLOOKUP(table_array, col_index_num, lookup_value, range_lookup)

• VLOOKUP(lookup_value, col_index_num, table_array)

Observe the image. It is a list of animals and their speed in mph. If you type an animal name cell E1, the respective speed will be displayed in cell E2. Select the correct Formula used here.

• =IF(A1=A1, B1)

• =VLOOKUP(E1,A2:B7,2,FALSE)

• =VLOOKUP(A1,A1:A7,1,FALSE)

• =VLOOKUP(E2,A1:B7,1,TRUE)

What is the last argument for the MATCH function?

• Lookup_value

• Match_type

• Lookup_array

• Area_num

What function is used to find price of Product_2?

• HlookUp

• Index

• Match

• VlookUp

What returns a position of a cell?

• Index

• Match

• Column

• Lookup

In Excel you can get validation on which tab?

• Edit

• Insert

• Data

• Formulas

What match type returns the exact value?

• 0(zero)

• 1(positive one)

• -1(negative one)

• 2 (two)

What is the syntax for index?

• =index(row num, array, col num)

• =index(array, row num, col num)

• =index[array, row num, col num]

• =index[ col num, array, row num]

What formula is used to find East_01?

• =HLOOKUP(A11, B1:B9,2, TRUE)

• =VLOOKUP(A11, A1:B9,2, TRUE)

• =HLOOKUP(A11, A1:B9,2, FALSE)

• =VLOOKUP(A11, A1:B9,2, FALSE)