Go Back

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.

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

Hello. I cannot get this formula to work. The two address functions work fine independently but not inside of the Median function. I suspect it's due to the fact that the Address functions result in a text value and the colon separates the two functions, but I'm baffled. Your assistance is appreciated. =MEDIAN(ADDRESS(COUNTA(B:B)-5,2):ADDRESS(COUNTA(B:B),2))
Solved by E. W. in 14 mins
Manipulating data in a pivot table. Want to use pivot table to get applicable email address next to each row - prefer not to use lookup formula
Solved by D. H. in 15 mins
I need help with a V lookup. I am trying to get a value from two tables and multiply the result in the third table
Solved by O. J. in 30 mins

Leave a Comment

avatar