Return the Cell Reference instead of the Value from a Lookup

In Excel, Lookup functions such as Vlookup or Index and Match can be used to find a record and return a value. But what if you want to return the cell reference, or address, instead of the value.

In this example we will look for a Customer using its ID and return the address of the cell that contains the customer’s city.

Formula to Return the Cell Reference

The formula below can be used to return the address of the cell that contains the city for the customer you are looking for on the Customers sheet.

=ADDRESS(MATCH(A1,Customers!A:A,0),5)

For example, if E1016 was entered into cell A1, then $E$7 would be returned.

The Explanation

The MATCH function is used for the lookup part of the formula. The MATCH function will look for the customer and return the row number the ID is in.

It is important to note that the VLOOKUP function (the most popular lookup function in Excel) will not work in this example, as it cannot be combined with the ADDRESS function.

The ADDRESS function is then used to create a cell reference as text from the info returned by the MATCH function.

The 5 on the end of the ADDRESS function is the column number to use in the reference. Another MATCH function could be used in this argument to find the column number if necessary.

The cell address is returned as an absolute cell reference by default. The ADDRESS function contains more arguments for you to specify different parameters if needed.

The example below shows a 4 being added to the ADDRESS function in the formula above to return the cell address as a relative cell reference.

=ADDRESS(MATCH(A1,Customers!A:A,0),5,4)

Watch the Video

Check out this list of the most common Excel functions to learn more and expand your Excel formula skills.