Excel Formula Help - MATCH for finding entries in large tables
MATCH is a useful function to learn when getting to know Microsoft Excel. MATCH performs a lookup and list function that helps when trying to track down an entry in a large table.
The data set below is openly available from Baltimore City and shows us the salaries for city employees for 2011. The list is very long, extending to 14454 entries.
We would like to find line numbers for three employees listed to the right of the main table. They are, Jackson,Bayda, Crosby,Mary L and Abraham,Sharon, M.
Importantly, when we crate a MATCH for these names, they must be in the correct format, which for this spread sheet is family name, comma (no space) immediately followed by first name, then space and middle initial if required.
As can be seen, in cell 17I, we have the formula =MATCH(I13,A1:A14455) which follows the format =MATCH(lookup _ value,lookup _ array)
The formula successfully locates the name at row 7.
You may question why we have 14454 entries, yet the range is set to 14455. As we can see, this takes us from the column header Name, A1. We do this because MATCH gives us the row number according to its lookup_array. For example, a MATCH array from A6 to A10 which finds a match in A8 will return a value of 3. The same is true for our lookup _array field here, if we start from A2, our line returned will always be one short.
To change the search, simply edit the formula in 17I by changing the lookup_value, as seen here, selecting a new cell and name:
When using numbers instead of names, we can use a switch at the end of the formula to determine how MATCH behaves. These switches change the formula format to =MATCH(lookup _ value,lookup _ array,lookup _ type). There are three types:
- if the Match _ type = 1 or is omitted: MATCH finds the largest value that is less than or equal to the Lookup _value. The Lookup _ array data must be sorted in ascending order.
- if the match _ type = 0: MATCH finds the first value that is exactly equal to the Lookup _ value. The Lookup _ array data can be sorted in any order.
- if the Match _ type = -1: MATCH finds the smallest value that is greater than or equal to the Lookup _ value. The Lookup _ array data must be sorted in descending order.
For further help and consultation, contact our experts. Help is also available on the Microsoft Excel reference pages here.
