You can use a lookup formula to return the address of a cell instead of a value within an Excel spreadsheet. You would usually be doing this to feed another function with the cell address.

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

Using the ADDRESS Function

The Excel ADDRESS function will be used to return the address of the cell when the value is found. The function is written as follows;

=ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])

Row_num: The row number of the cell.

Column_num: The column number of the cell.

Abs_num: The type of reference you want to return. By default, the ADDRESS function will return an absolute reference such as $F$12. The other options are to return an absolute row/relative column, relative row/absolute column or a relative reference. You make your choice by entering a number between 1 and 4.

a1: This is the reference style. Enter 0 for an R1C1 style such as R12C6 (this is cell F6), or enter 1 for an A1 style which is the classic F6. The A1 style is the default.

Sheet_text: The name of a worksheet to use in the reference.

Formula to Find the Cell Address

The Excel ADDRESS function is combined with the MATCH function to find the cell address of a value.

=ADDRESS(MATCH($I$4,$A$1:$A$92,0),6)

The MATCH function is used to look for the row number of the customer. The column number is entered as 6. Another MATCH function could have been used to locate the column number also.

No other arguments have been used meaning the answer will be returned using the defaults of an absolute cell reference, in A1 style and with no sheet text.

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

=ADDRESS(MATCH($I$4,$A$1:$A$92,0),6,4)

Watch the Video

Excel lookup functions such as VLOOKUP and MATCH are not case sensitive. They will not worry about matching the case of the lookup value and the cell entries when they search.

But what if you need the cases to match? Well let’s have a look.

Ensuring an Exact Match in the Lookup

To ensure we have an exact match we can use the EXACT function. This function compares two strings of text to see if they are the same, and if they are it returns TRUE, but if they are different it will return FALSE.

The EXACT function looks like this;

=EXACT(text1, text2)

Text1 and Text2 represent the two text strings that you want to compare. In our example these will be the lookup value, and the range of cells we are looking in.

Because we are going to be asking the EXACT function to test one piece of text against a range of text entries, our lookup formula will be entered as an array formula. This special type of formula is entered by pressing Ctrl + Shift + Enter, instead of just Enter and are sometimes referred to as CSE formulas.

Create a Lookup Function with Exact Match

The INDEX and MATCH functions will be used for the lookup in this example. These two functions offer a very versatile Excel lookup formula. If you use functions like VLOOKUP on your spreadsheets and are not familiar with these, I suggest checking them out. You will not be disappointed (Learn more about INDEX and MATCH)

In the example below, we want to return the city that a customer is based in from a list using the customer ID. We want to force the customer ID to be entered using the correct case.

The Excel lookup formula below is used to create a case sensitive lookup for an ID entered in cell H4.

You do not type the curly braces when writing the formula. Excel will input these when you press Ctrl + Shift+Enter.

The MATCH function is looking for the value of TRUE. This is because the EXACT function has been used to test the customer ID against all the ID’s in column A. The value TRUE is returned if an exact match is found.

Watch the Video – Case Sensitive Lookup Formula

You may require a multiple condition lookup formula in Excel to perform a lookup based on the values in multiple columns. For example, in the image below the formula in cell B3 returns the code based on the country and area specified in cells B1 and B2.

Lookup a Value using Multiple Conditions

This example uses an array formula meaning that instead of pressing Enter to run the formula, you should press Ctrl + Shift + Enter. You do not type the curly braces shown in the formula.

The INDEX and MATCH functions are used for this lookup formula (Learn about INDEX and MATCH).

The formula below will return the code if the country matches the one entered in cell B1, and the area matches the one entered in cell B2. This examples uses two conditions. You can include as many conditions as you need using the same technique describe in this tutorial.

The two conditions are entered inside brackets within the lookup_array argument of the MATCH function. The * symbol has been used to apply the AND logic between the two conditions. The + symbol can be used to apply the OR logic.

The result of the two tests will be returned as a 1 for TRUE, or a 0 for FALSE. The MATCH function uses a 1 for its lookup_value so that it returns the row number for the record where both conditions are met.

Watch the Video – Multiple Condition Lookup Formula