=index() and =match()

=INDEX (array, row_num, [col_num], [area_num])

array - a range of cells or an array constant. row_num - the row number from which the value is to be fetched. [col_num] - the column number from which the value is to be fetched. Although this is an optional argument, but if row_num is not provided, it needs to be given. [area_num] - (Optional) If array argument is made up of multiple ranges, this number would be used to select the reference from all the ranges.

=INDEX() usage

can find cell entry if given row and column numbers (like latitude/longitude)

=MATCH() purpose

finds the location of a cell based on a lookup value, so essentially finds POSITION rather than value (as compared to =VLOOKUP() or =XLOOKUP()

=MATCH(lookup_value, lookup_array, [match_type])

lookup_value - The value for which you are looking for a match in the lookup_array (text needs quotes) lookup_array - The range of cells in which you are searching for the lookup_value. [match_type] - (Optional) This specifies how excel should look for a matching value. It can take three values -1, 0 , or 1, where: 0 - this will look for an exact match of the value. If an exact match is found, the MATCH function will return the cell position. Else, it will return an error. 1 - this finds the largest value that is less than or equal to the lookup value. For this to work, your data range needs to be sorted in ascending order. -1 - this finds the smallest value that is greater than or equal to the lookup value. For this to work, your data range needs to be sorted in descending order.

=INDEX($B$2:$D$11, MATCH($H$11, $A$2:$A$11, 0), MATCH($I$10, $B$1:$D$1, 0))

using data ranging from columns B to D and rows 2 to 11, find the exact (0) row corresponding to the name I write in H11 (names listed in column A), and then find the exact (0) value from that row for the column name that I write in I10 (column names listed in row 1) *essentially using match() to find both row and column position in order to ultimately find the entry of the cell in that position!

=INDEX($A$2:$B$11,MATCH("Jim",$A$2:$A$11,0),2)

using the data located from columns A to B and rows 2 to 11, find the exact (0) row that says "Jim" within column A, and then return that row's corresponding value in the second column *essentially using =MATCH() to find row value!

=SUM(INDEX(B3:E5,0,1)

while =index(B3:E5) would only return the first value in column B (row=0), =SUM(INDEX(B3:B5,0,1) will add the entire column B