While working with large tables or databases in Excel, sometimes you might need to find out whether an item appears on a list or column or not. The most common way of solving this problem is by using the Find and Replace functionality (Ctrl + F). This brings up a dialogue box that enables you to enter the name of the item you are looking for, and get in return all the reference locations of the instances of that item in your view.

A second way to search is to enable dropdown filters on your database by selecting the entire range of cells you are conducting the search on (or just the first row of cells in the range – the headers) and pressing Ctrl + Shift + L. Also, converting your search range into an Excel Table (the keyboard shortcut is to select the entire range and press Ctrl + T) achieves the same task. The dropdown filters will allow you to type in and search for items, or scroll down a summarized list of items under that column to find out if the target item is on the list.

If you not only wanted to find out whether an item was on a list, but its position on the list you would use the =MATCH() function. It works as follows:

=MATCH(“name of item”,”range of cells within which we are searching for items (must be a list that falls within a single column or row),”match accuracy – 0 for perfect match, 1 for partial match”)

The =MATCH() function returns a number that indicates the item’s order on the list.

For certain searches, you not only want to know if an item is on a list, but the cell address of the list it is on. A combination of the =MATCH() function above and an ADDRESS() function can get the job done for you.

A cell address consists of both a column position and a row position. To run this formula, either the column or row position must already be known. In the example below, the known position is the column – column J. =ADDRESS() function works for this instance as follows:

=ADDRESS(“row number”,”column number”)