## Lookups in a related table (array formula)

The "new" excel 2010 powerpivot feature and DAX formulas lets you work with multiple tables of data. You can connect tables to each other by relationships. When relationships are made nothing stops you from doing lookups to related values and related tables or sum values for a related table.

This post is not about powerpivot and DAX formulas, it is about doing lookups in two tables and they have one column in common. I´ll also show you how to sum values in a related table.

### Search for values in a related table

**Array formula in cell C14:**

**How to create an array formula**

- Select cell C14
- Copy above array formula
- Click 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**

**Step 1 - Search for a value **

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**

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**

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**

=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.

### Sum values in a related table

**Array formula in cell C14:**

How to create an array formula

**Download excel *.xlsx file**

### Category: Related tables

Working with three related tables

I have written a few posts about two related tables and today I am going to show you how to […]Comments(10) Filed in category: Excel, Related tables

Comments(7) Filed in category: Excel, Pivot table, Related tables

Extract unique distinct values from a related table

In a previous post I described how to do lookups in a related table. In this post I am going […]Comments(7) Filed in category: Excel, Related tables

Applying conditional formatting to related tables

It can be really hard trying to follow a lookup in related tables. I will show you how to use […]Comments(1) Filed in category: Excel, Related tables

Search two related tables simultaneously (vba)

Let´s say you do a lot of searches in two tables. The tables are related so it would be great […]Comments(0) Filed in category: Excel, Related tables

### 3 Responses to “Lookups in a related table (array formula)”

### Leave a Reply

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

<code>your formula</code>

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

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

VBA code

[/vb]

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

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

[...] 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 […]