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

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

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

This post demonstrates how to merge two related tables before creating a pivot table. A pivot table is limited to […]

Use Conditional Formatting to do lookups in related tables

It can be really hard trying to follow a lookup in related tables. I will show you how to use […]

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

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

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