# Lookups in relational tables

Excel 2010 has a PowerPivot feature and DAX formulas that let you work with multiple tables of data. You can connect tables to each other based on relationships. When relationships are made nothing stops you from doing lookups to related values and relational tables or sum values for a relational table.

#### Table of Contents

## 1. Introduction

### What is a relational table?

*In a relational database (Microsoft Access), the data in one table is related to the data in other tables. In general, tables can be related in one of three different ways: one-to-one, one-to-many or many-to-many.Â The relationship is used to cross-reference information between tables.*

Source: University of Sussex

This post is not about PowerPivot and DAX formulas, it is about doing lookups in two tables and they have one column in common. This means that the columns contain the same values, however, not necessarily in the same order. This makes the two data sets related because they share a value.

The image above shows a formula in cell C14 that looks for a value, specified in cell C12, in B3:B7. The corresponding value in C3:C7 is then used do a lookup in E3:E9. The formula then returns the corresponding values in F3:F9 to C14:C16. This is possible because they share the same values in column C and in column E.

Here is an example, cell C12 contains Apple, Apple is found in cell C3 and C5. The corresponding values in C3:C7 are 1 and 3. The formula looks for 1 and 3 in E3:E9 and finds cell E3, E5 and E6. Now the formula returns values from the same rows to C14:C16, the values are "Mainco", "Matsu Fishworks" and "Culdee Fell".

I'll also demonstrate a formula that sums values in a relational table.

## 2. Search for values in a related table

The animated image above explains how the concept works. The following formula can return a single value but it returns multiple values if more values match. Not only does it match multiple values in the first table, it also matches multiple values in the related table as well.

Array formula in cell C14:

**How to create an array formula**

- Select cell C14
- Copy above array formula
- Press with left mouse button on in formula bar
- Paste array formula
- Press and hold Ctrl + Shift
- Press Enter

**How to copy array formula**

- Select cell C14
- Copy (Ctrl + c)
- Select cell range C15:C17
- Paste (Ctrl + v)

**Explaining array formula in cell C14**

#### How can I examine formula calculations in greater detail?

You can follow formula calculations quite easily using the "Evaluate Formula" feature in Excel. Select cell C14 and then go to tab "Formulas" on the ribbon. Press with mouse on "Evaluate Formula" button to open an "Evaluate" dialog box.

(*The formula shown above in the Evaluate Formula" dialog box is not used in this article.*)

Press with left mouse button on "Evaluate" button to see the next calculation step. I have demonstrated these calculations steps below.

#### Step 1 - Search for a value

The IF function has three arguments. IF(*logical_test*, [*value_if_true*], [*value_if_false*])

The logical_test argument contains an expression that either returns TRUE or FALSE, in this case the value in cell C12 is compared to all values in cell range B3:B7.

The corresponding value in cell range C3:C7 is returned if the expression returns TRUE and nothing is returned if FALSE.

IF($C$12=$B$3:$B$7, $C$3:$C$7, "")

becomes

IF("Apple"={"Apple"; "Banana"; "Apple"; "Lemon"; "Banana"}, {1; 2; 3; 4; 5}, "")

and returnsÂ {1;"";3;"";""}.

#### Step 2 - Use column in common to find matches

The MATCH function returns a number representing the relative position if a value exists in a cell range or array. It returns #N/A if not found.

MATCH($E$3:$E$9, IF($C$12=$B$3:$B$7, $C$3:$C$7, ""), 0)

becomes

MATCH($E$3:$E$9, {1;"";3;"";""}, 0)

becomes

MATCH({1; 2; 1; 3; 4; 5; 5}, {1;"";3;"";""}, 0)

and returns

{1;#N/A;1;3;#N/A;#N/A;#N/A}

#### Step 3 - Return row numbers

The ISERROR function is used to identify error values in the array, the IF function replaces error values with a blank "" and numbers with the corresponding row number.

IF(ISERROR(MATCH($E$3:$E$9, IF($C$12=$B$3:$B$7, $C$3:$C$7, ""), 0)), "", MATCH(ROW($F$3:$F$9), ROW($F$3:$F$9)))

becomes

IF(ISERROR({1;#N/A;1;3;#N/A;#N/A;#N/A}, "", MATCH(ROW($F$3:$F$9), ROW($F$3:$F$9)))

becomes

IF(ISERROR({1;#N/A;1;3;#N/A;#N/A;#N/A}, "", {1; 2; 3; 4; 5; 6; 7})

and returnsÂ {1; ""; 3; 4; ""; ""; ""}

#### Step 4 - Return a value of the cell at the intersection of Â a particular row and column

The corresponding row number is used by the INDEX function to return a specific value based on row and column numbers. The SMALL function extracts a row number based on a relative cell reference and the ROW function. The relative cell reference changes when the cell is copied and pasted to cells below.

=INDEX($F$3:$F$9, SMALL(IF(ISERROR(MATCH($E$3:$E$9, IF($C$12=$B$3:$B$7, $C$3:$C$7, ""), 0)), "", MATCH(ROW($F$3:$F$9), ROW($F$3:$F$9))), ROW(A1)))

becomes

=INDEX($F$3:$F$9, SMALL({1; ""; 3; 4; ""; ""; ""}, ROW(A1)))

becomes

=INDEX($F$3:$F$9, 1)

becomes

=INDEX({"Mainco"; "Gadgetron"; "Matsu Fishworks"; "Culdee Fell"; "Trade Federation"; "KrebStar"; "Monarch Co."}, 1)

and returns Mainco in cell C14.

## 3. Search for values in a related table - Excel 365

Excel 365 dynamic formula in cell F12:

### Explaining the formula in cell F12

#### Step 1 - Compare values

The equal sign lets you compare value to value, in this case, value to values. The result is an array containing boolean values TRUE or FALSE.

B3:B7=C12

becomes

{"Apple"; "Banana"; "Apple"; "Lemon"; "Banana"}="Apple"

and returns

{TRUE; FALSE; TRUE; FALSE; FALSE}

#### Step 2 - Filter values based on a condition

The FILTER function extracts values/rows based on a condition or criteria.

Function syntax: FILTER(array, include, [if_empty])

FILTER(C3:C7,B3:B7=C12)

becomes

FILTER({1; 2; 3; 4; 5},{TRUE; FALSE; TRUE; FALSE; FALSE})

and returns

{1; 3}

#### Step 3 - Compare filtered values to values in the second table

The MATCH function returns the relative position of an item in an array that matchesÂ a specified value in a specific order.

Function syntax: MATCH(lookup_value, lookup_array, [match_type])

MATCH(E3:E9,FILTER(C3:C7,B3:B7=C12),0)

becomes

MATCH({1; 2; 1; 3; 4; 5; 5},{1; 3},0)

and returns

{1; #N/A; 1; 2; #N/A; #N/A; #N/A}

#### Step 4 - Check if an error has occurred

The IFNA function handles #N/A errors only, it returns a specific value if the formula returns a #N/A error.

Function syntax: IFNA(value, value_if_na)

IFNA(MATCH(E3:E9,FILTER(C3:C7,B3:B7=C12),0),0)

becomes

IFNA({1; #N/A; 1; 2; #N/A; #N/A; #N/A},0)

and returns

{1; 0; 1; 2; 0; 0; 0}

#### Step 5 - Filter values based on an array

It is possible to filter an array using numbers and not boolean values.

TRUE is the same as any number except 0 (zero).

FALSE is 0 (zero).

FILTER(F3:F9,IFNA(MATCH(E3:E9,FILTER(C3:C7,B3:B7=C12),0),0))

becomes

FILTER({"Mainco"; "Gadgetron"; "Matsu Fishworks"; "Culdee Fell"; "Trade Federation"; "KrebStar"; "Monarch Co."},{1; 0; 1; 2; 0; 0; 0})

and returns

{"Mainco"; "Matsu Fishworks"; "Culdee Fell"}

## 4. Sum values in a related table - earlier Excel versions

This example shows a formula that searches data in G3:G9 for a value specified in C12, uses the corresponding value on the same row in F3:F9 to match values in cell range C3:C7, and adds values on the same rows in cell range D3:D7 to calculate a total.

Formula in cell C14:

### Explaining formula in cell C14

#### Step 1 - Find the relative position in the array

The MATCH function returns the relative position of an item in an array that matchesÂ a specified value in a specific order.

Function syntax: MATCH(lookup_value, lookup_array, [match_type])

MATCH(C12,G3:G9,0)

becomes

MATCH("Gadgetron",{"Culdee Fell"; "Gadgetron"; "KrebStar"; "Mainco"; "Matsu Fishworks"; "Monarch Co."; "Trade Federation"},0)

and returns 2.

#### Step 2 - Get value in cell range F3:F9

The INDEX function returns a value or reference from a cell range or array, you specify which value based on a row and column number.

Function syntax: INDEX(array, [row_num], [column_num])

INDEX(F3:F9,MATCH(C12,G3:G9,0))

becomes

INDEX(F3:F9,2)

and returns 2.

#### Step 3 - Get value in cell range F3:F9

The SUMIF function sums numerical values based on a condition.

Function syntax: SUMIF(range, criteria, [sum_range])

SUMIF(C3:C7,INDEX(F3:F9,MATCH(C12,G3:G9,0)),D3:D7)

becomes

SUMIF(C3:C7,2,D3:D7)

and returns 300.

## 5. Sum values in a related table - Excel 365

This example demonstrates an even smaller formula than the one in section 3. You need Excel 365 to use this formula.

Formula in cell C14:

### Explaining the formula in cell C14

#### Step 1 - Compare values

The equal sign lets you compare values in an Excel formula, the result is either TRUE or FALSE.

C12=G3:G9

becomes

"Gadgetron"={"Culdee Fell"; "Gadgetron"; "KrebStar"; "Mainco"; "Matsu Fishworks"; "Monarch Co."; "Trade Federation"}

and returns

{FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE}

#### Step 2 - Filter values

The Filter function extracts values/rows based on a condition or criteria.

Function syntax: FILTER(array, include, [if_empty])

FILTER(F3:F9,C12=G3:G9)

becomes

FILTER(F3:F9,{FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE})

and returns 2.

#### Step 3 - Sum values

The SUMIF function sums numerical values based on a condition.

Function syntax: SUMIF(range, criteria, [sum_range])

SUMIF(C3:C7,FILTER(F3:F9,C12=G3:G9),D3:D7)

becomes

SUMIF({300;200;400;100;300},**2**,{1;2;3;2;5})

and returns 300.

## 7. Search two related tables - VBA

This article demonstrates a macro that automatically applies a filter to an Excel defined Table based on the result from a second Excel defined Table.

Let's say you do a lot of searches in two tables. The tables are related so it would be great if the second table is simultaneously filtered depending on the filtered values from the first table.

Two data sets are related if they share at least one column, that makes it possible to perform two searches or lookups.

Example,

You want to know the contact information to all vendors in product module 3. You select Module 3 in column "Product module" and vendor names appear in column "Vendor".

But contact information to each vendorÂ is in table 2, sheet "Vendors". The macro demonstrated in the animated picture below filters table2 automatically.

The following event code is rund when worksheet "Vendors" is activated.

'Event code that runs when worksheet is activated (selected) Private Sub Worksheet_Activate() 'Dimension variables and declare data types Dim temp() As Variant Dim rng As Range Dim b As Boolean Dim i As Single 'Redimension variable temp to make it possible to add more values later on in this macro ReDim temp(0) 'Don't show changes on screen Application.ScreenUpdating = False 'Save the fourth column in Table1 to object variable rng Set rng = Worksheets("Modules").ListObjects("Table1").ListColumns(4).Range 'Copy filtered values from Table1 to growing array 'Iterate through cells in cell range except header cell For i = 2 To rng.Cells.Count 'Check if value is not equal to nothing If rng(i).Value <> "" Then 'Check that row is not filtered out If rng(i).EntireRow.Hidden = False Then 'Save value to array variable temp temp(UBound(temp)) = rng(i).Value 'Increase size of array variable temp ReDim Preserve temp(UBound(temp) + 1) Else 'Save boolean value True to variable b, this will apply a filter to the other Excel defined Table later on in this event code b = True End If End If Next i 'Remove last container from array variable temp ReDim Preserve temp(UBound(temp) - 1) 'Remove previously selected filters in table2 Worksheets("Vendors").ListObjects("Table2").Range.AutoFilter Field:=1 'Check if variable b is False and stop this macro if so If b <> True Then Exit Sub 'Apply filtered values to table 2 Worksheets("Vendors").ListObjects("Table2").Range.AutoFilter _ Field:=1, Criteria1:=temp, Operator:=xlFilterValues 'Show changes to Excel user Application.ScreenUpdating = True End Sub

### 7.1 Where to put the code?

- Copy above event code.
- Press Alt + F11 to open the Visual Basic Editor.
- Doublepress with left mouse button on a worksheet in your workbook to open the worksheet module.
- Paste code to worksheet module.

### Related tables category

In this post, I am going to show you how to extract unique distinct values and duplicates using a formula, […]

I will in this article demonstrate four formulas that do lookups, extract unique distinct and duplicate values and sums numbers […]

This article demonstrates how to merge two relational data sets before creating a Pivot table. A Pivot Table is limited […]

### Excel categories

### 3 Responses to “Lookups in relational tables”

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

[...] on Oct.17, 2012. Email This article to a Friend In a previous post I described how to do lookups in a related table. In this post I am going to show you how to extract unique distinct values and duplicates from a [...]

[...] values on Oct.19, 2012. Email This article to a Friend I have written a few posts about two related tables and today I am going to show you how to work with three related tables:Lookups in three related [...]

[…] Search for values in a related table […]