## Lookups in a related table (array formula)

*Article updated on July 29, 2017*

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

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

Remember to convert less than and larger than signs to html character entities before you post your comment.

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