How to Get Address of Lookup Result

Generally, we look up for the required information in a large dataset using INDEX, MATCH functions and return the matching value based on given criteria. But if we want to look up a value and return the cell reference or the cell address of resulting value then we need to use the CELL function. In this article, we will learn how to lookup for a value and get the address of lookup result using the CELL function.

Figure 1. Final result

CELL Function

The CELL function gives information about a specific cell in a worksheet. That information can either be things like address and filename or even much more detailed, such as cell formatting, etc.

CELL Syntax:

=CELL(“address”,[reference])

This formula syntax uses following arguments to get the address of lookup result:

• address (required): This is the type of information that you are returning about the reference.
• reference (optional): This is a reference or range of cells to return the lookup result

Lookup Result

As per the formula syntax, the CELL function requires the cell reference of lookup result and normally the INDEX function returns the cell values of the lookup result. However, the INDEX function returns the cell reference of the lookup result. Therefore, we need to use the INDEX and MATCH functions for the lookup results and then wrap them inside the CELL function as a reference argument to get the address of the lookup result. For example, we have a data set of various products in inventory and we need a lookup result of price for a lookup ID 120 in cell H3 using the following INDEX, MATCH formula:

=INDEX($A$2:$E$10,MATCH(H2,$A$2:$A$10,0),5)

Figure 2. Lookup Result of Price of an item

Get Address of Lookup Result

Now, we want to get the address of lookup result of price as mentioned in cell H3. In order to do that we will wrap the INDEX, MATCH formula in CELL function using “address” option in cell H4 in the following formula.

=CELL("address",INDEX($A$2:$E$10,MATCH(H2,$A$2:$A$10,0),5))

The INDEX function returns the reference of lookup result of price in range A2: E10 based on row and column number. The MATCH function returns the relative position (row number) for lookup ID 120 in range A2: A10. Since the price is listed in the 5th column of range A2: E10, we need to mention the column_num argument as 5. When we wrap the INDEX, MATCH functions inside the CELL function with “address” option it returns the cell address of lookup result of price in cell H4. It means that lookup result is present in $E$5 as per this example.

Figure 3. Cell address of lookup result of Price

Instant Connection to an Expert through our Excelchat Service:

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.