Excel: Lookup Functions in Depth

Locate data with the MATCH function - Excel: Lookup Functions in Depth Video Tutorial | LinkedIn…

=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
• Lets say you want to search for the name “Rick” from “McGee, Rick”,
  type “*, “&Rick
• * means multiple characters infront of the word “Rick”

=FORMULATEXT( )

• This is one way to show the formulas

=vlookup()

• return column results

=hlookup()

• for horizontal data
• make sure data is in ascending order from left to right
• row index number refers to which row to output

=vlookup()

• for vertical data
• make sure data is in ascending order from top to bottom
• column index number refers to which column to output

=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.
• E.g. “*P*” means you are finding a string that could contain the letter ‘P’ which may be mix with other wildcard characters.
• to sum a row of numbers, select the cells and press ‘alt’ + ‘=’

=choose()

• this formula, we provide it with all possible outcomes/answers and the formula will choose by itself the output.
• to show the formula, we can select a cell and put a space before the “=” formula.
• using lookup, you will need assitant of an external table. but choose formula does not require an external table.
• Limitation is that it can only use numbers as a option chooser.
• The formula is lengthy, but you do not need any external table for the formula to work.

=switch()

• works like a dictionary in python. If the expression meet the key, the formula will return the value.
• same as choose formula, we dont need an external table.
• The formula is lengthy, but you do not need any external table for the formula to work.

=unique()

• returns unique rows.
• use to return elements that repeat only once.

=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”
• * means ‘AND’ not a wild char or multiply!!!

=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.
• It allows an indirect formula to be written.