Lookup Tables

Today’s tip is for assigning groups people or items to groups/centers/locations.  I like to use the lookup function in Excel to place a group of students to specific assignments or locations.  A lookup table can be used to assign letter grades to specific percentages also.  

Actually, Excel can Lookup many different types of values in a table format.

In the spreadsheet above I show an example of assigning students to groups for a weekly rotation. I have a list of students and assign them to a group. In cell F2 I have a lookup function =LOOKUP(C:C,L:L,M:M). 

This function tell cell F2 to look at column C, find a match in column L and return the value in column M.  Then I can copy the formula for every student in the column.  Then if you decide you want to change the assignment for Group A on Monday you only need to make the change in cell M3 and the function will do the rest to the cells with the function available.

Use the lookup table to lookup many variables that might change over time when you have a long list to update.  Have fun customizing your own spreadsheet for classes, over school events, or organizing stuff at home.

For more info on functions watch: Excel Functions

LOOKUP function (description from Microsoft help)

The LOOKUP function returns a value either from a one-row or one-column range (range: Two or more cells on a sheet. The cells in a range can be adjacent or nonadjacent.) or from an array (array: Used to build single formulas that produce multiple results or that operate on a group of arguments that are arranged in rows and columns. An array range shares a common formula; an array constant is a group of constants used as an argument.). The LOOKUP function has two syntax forms: the vector form and the array form.

·         For the LOOKUP function to work correctly, the data being looked up must be sorted in ascending order.

Vector form

A vector is a range of only one row or one column. The vector form of LOOKUP looks in a one-row or one-column range (known as a vector) for a value and returns a value from the same position in a second one-row or one-column range. Use this form of the LOOKUP function when you want to specify the range that contains the values that you want to match.

LOOKUP(lookup_value,  lookup_vector,  [result_vector])

·         lookup_value    Required. A value that LOOKUP searches for in the first vector. Lookup_value can be a number, text, a logical value, or a name or reference that refers to a value.

·         lookup_vector    Required. A range that contains only one row or one column. result_vector    Optional. A range that contains only one row or column.

Note:

·         If the LOOKUP function can't find the lookup_value, the function matches the largest value in lookup_vector that is less than or equal to lookup_value.

·         If lookup_value is smaller than the smallest value in lookup_vector, LOOKUP returns the #N/A error value.