Return to Excel Formulas List

Lookup – Return Cell Address Instead of Value – Excel & Google Sheets

Download Example Workbook

Download the example workbook

This tutorial will demonstrate how to retrieve the address of a looked-up cell instead of the value

lookup cell address

 

Lookup Cell Address

To look up a cell address (instead of a cell value), we use the INDEX / MATCH Functions to find the range and the CELL Function to output the cell address:

=CELL("address",INDEX(B2:F11,MATCH(I2,B2:B11,0),3))

Return Cell Address Instead of Value 01

We will walk through the formula below.

MATCH Functions

The MATCH Function looks for the invoice number and returns its row number:

=MATCH(E2,B2:B11,0)

Return Cell Address Instead of Value- Match

 

INDEX Function

The INDEX Function returns the cell value corresponding to the row returned by the MATCH Function above:

=INDEX(B2:F11,MATCH(I2,B2:B11,0),3)

Return Cell Address Instead of Value INDEX

 

CELL Function – Retrieve Cell Attributes

The CELL Function retrieves specific attributes about a cell. Here we calculate the cell address by inputting “address”:

=CELL("address",INDEX(B2:F11,MATCH(I2,B2:B11,0),3))

Return Cell Address Instead of Value 01

Lookup – return cell address instead of value in Google Sheets

The example above works the same way in Google Sheets.

Excel Lookup Return Cell Address Inst Google Function