Excel: Lookup Functions in Depth

=match()

  • find the match you want and return the row where the result is at.

=index()

  • provide the formula with the row and column and it will search through the array table and return the results.

=counta()

  • count all cells that are not empty

=index( , counta())

  • gets the last data

=index( , =match(), )

  • equivalent to a leftward V-lookup

=xmatch()

  • used to search first/last data by specifying how you want to search from. ie. top to bottom or bottom to top

=FORMULATEXT( )

  • This is one way to show the formulas

=vlookup()

  • return column results

=hlookup()

  • for horizontal data

=vlookup()

  • for vertical data

=xlookup()

to view the formula guide, type =xl + tab

  • can be used to replace both Hlookup and Vlookup.

xlookup approximate worksheet

  • if you dont need to fill up an argument in the formula just fill it with a comma and skip to fill the next argument.

*shortcut: in a column by selecting a cell and double click on the bottom of the cell when you see the 4 arrows will bring you to the last cell in the column. *vice versa in a column by selecting a cell and double click on the top of the cell when you see the 4 arrows will bring you to the first cell in the column.

  • for the return array, you can return as many things as you want by specifying the array.

=choose()

  • this formula, we provide it with all possible outcomes/answers and the formula will choose by itself the output.

=switch()

  • works like a dictionary in python. If the expression meet the key, the formula will return the value.

=unique()

  • returns unique rows.

=filter()

  • to copy just the format of an array, just select the array + right-click hold and drag to the cells you want to format + select “copy here as formats only”

=sort()

  • sort a column by the first column default

=sortby()

  • allows you to sort by more than 1 criteria

=indirect()

  • allows you to reference other worksheets.

Some beautiful paths can’t be discovered without getting lost.