## 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 work with three related tables:

- Lookups in three related tables and return multiple values
- Filter unique distinct values from three related tables
- Filter duplicates from three related tables
- Sum values from three related tables

### Lookups in three related tables and return multiple values

The picture below is a gif animation. It shows you how the array formula works.

The array formula looks for the Item "Router" in Item column in the first table (column B and C). The corresponding Category is B.

In table two (column E and F) there are 4 matches to Category "B". The corresponding companies in table 2 are "Gizmonic Institute", "Ewing Oil", "Sample Company" and "Mishima Zaibatsu".

In table 3 (column H and I) there are six matches to category "Company" and the adjacent Sales persons are returned in cell range C21:C27.

**Array formula in cell range C21:**

**How to create an array formula**

- Copy above array formula
- Select cell C21
- Click in formula bar

- Paste array formula (Ctrl + v)
- Press and hold Ctrl+ Shift
- Press Enter
- Release all keys

The array formula is now surrounded by curly brackets, like this: {=array_formula}

**How to copy array formula**

- Select cell C21
- Copy cell (Ctrl + c)
- Select cell range C22:C27
- Paste (Ctrl + v)

### Explaining lookup array formula in cell C21

**Step 1 - Check whether a condition is met and return corresponding Table1[Category] value**

IF($C$19=Table1[Item],Table1[Category],"")

becomes

IF("Router" = {"Drill driver"; "Angle grinder"; "Jig saw"; "Gas frame nailer"; "Table saw"; "Router"; "Mitre saw"; "Jointing machine"; "Random orbit sander"; "Planer"; "Combi drill"; "Impact wrench"; "Circlar saw"; "Rotary hammer drill"; "Biscuit joiner"}, {"A"; "B"; "C"; "D"; "A"; "B"; "C"; "D"; "A"; "B"; "C"; "D"; "A"; "B"; "C"} , "")

and returns

{"";"";"";"";"";"B";"";"";"";"";"";"";"";"";""}

**Step 2 - Return the relative position of an item in an array that matches a specified value**

MATCH(Table2[Category], IF($C$19=Table1[Item], Table1[Category], ""), 0)

becomes

MATCH(Table2[Category], {"";"";"";"";"";"B";"";"";"";"";"";"";"";"";""}, 0)

becomes

MATCH({"A"; "B"; "C"; "A"; "D"; "B"; "A"; "C"; "D"; "B"; "C"; "C"; "B"; "A"; "D"}, {"";"";"";"";"";"B";"";"";"";"";"";"";"";"";""}, 0)

and returns

{#N/A; 6; #N/A; #N/A; #N/A; 6; #N/A; #N/A; #N/A; 6; #N/A; #N/A; 6; #N/A; #N/A}

**Step 3 - Check whether a condition is met and return corresponding Table2[Company] value**

IF(ISERROR(MATCH(Table2[Category], IF($C$19=Table1[Item], Table1[Category], ""), 0)), "", Table2[Company])

becomes

IF(ISERROR({#N/A; 6; #N/A; #N/A; #N/A; 6; #N/A; #N/A; #N/A; 6; #N/A; #N/A; 6; #N/A; #N/A}), "", Table2[Company])

becomes

IF(ISERROR({#N/A; 6; #N/A; #N/A; #N/A; 6; #N/A; #N/A; #N/A; 6; #N/A; #N/A; 6; #N/A; #N/A}), "", {"Global Dynamics"; "Gizmonic Institute"; "Initech"; "Northern Railway"; "Sixty Second Avenue"; "Ewing Oil"; "Sonky Rubber Goods"; "Foo Bars"; "Initech"; "Sample Company"; "Gizmonic Institute"; "Energy Corporation"; "Mishima Zaibatsu"; "Monarch Co."; "Industrial Automation"})

and returns

{""; "Gizmonic Institute"; ""; ""; ""; "Ewing Oil"; ""; ""; ""; "Sample Company"; ""; ""; "Mishima Zaibatsu"; ""; ""}

**Step 4 - Return the relative position of an item in an array that matches a specified value**

MATCH(Table3[Company], IF(ISERROR(MATCH(Table2[Category], IF($C$19=Table1[Item], Table1[Category], ""), 0)), "", Table2[Company]), 0)

becomes

MATCH({"Sample Company"; "Energy Corporation"; "Initech"; "Gizmonic Institute"; "Northern Railway"; "Sonky Rubber Goods"; "Sample Company"; "Monarch Co."; "Industrial Automation"; "Ewing Oil"; "Gizmonic Institute"; "Initech"; "Foo Bars"; "Sixty Second Avenue"; "Mishima Zaibatsu"}, {""; "Gizmonic Institute"; ""; ""; ""; "Ewing Oil"; ""; ""; ""; "Sample Company"; ""; ""; "Mishima Zaibatsu"; ""; ""}, 0)

and returns

{10; #N/A; #N/A; 2; #N/A; #N/A; 10; #N/A; #N/A; 6; 2; #N/A; #N/A; #N/A; 13}

**Step 5 - Check whether a condition is met and return row number**

IF(ISERROR(MATCH(Table3[Company], IF(ISERROR(MATCH(Table2[Category], IF($C$19=Table1[Item], Table1[Category], ""), 0)), "", Table2[Company]), 0)), "", MATCH(ROW(Table3[Sales persons]), ROW(Table3[Sales persons])))

becomes

IF(ISERROR({10; #N/A; #N/A; 2; #N/A; #N/A; 10; #N/A; #N/A; 6; 2; #N/A; #N/A; #N/A; 13}), "", MATCH(ROW(Table3[Sales persons]), ROW(Table3[Sales persons])))

becomes

IF(ISERROR({10; #N/A; #N/A; 2; #N/A; #N/A; 10; #N/A; #N/A; 6; 2; #N/A; #N/A; #N/A; 13}), "", {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15})

and returns

{1;"";"";4;"";"";7;"";"";10;11;"";"";"";15}

**Step 6 - Return the k-th smallest row number**

SMALL(*array*, *k*)

SMALL(IF(ISERROR(MATCH(Table3[Company], IF(ISERROR(MATCH(Table2[Category], IF($C$19=Table1[Item], Table1[Category], ""), 0)), "", Table2[Company]), 0)), "", MATCH(ROW(Table3[Sales persons]), ROW(Table3[Sales persons]))), ROW(A1))

becomes

SMALL({1;"";"";4;"";"";7;"";"";10;11;"";"";"";15}, ROW(A1))

and returns 1

*Step 7 - Return a reference of the cell at the intersection of a particular row and column*

INDEX(Table3[Sales persons], SMALL(IF(ISERROR(MATCH(Table3[Company], IF(ISERROR(MATCH(Table2[Category], IF($C$19=Table1[Item], Table1[Category], ""), 0)), "", Table2[Company]), 0)), "", MATCH(ROW(Table3[Sales persons]), ROW(Table3[Sales persons]))), ROW(A1)))

becomes

INDEX(Table3[Sales persons], 1)

becomes

INDEX({"Simeon Fernandes"; "Corydon Keck"; "Hija Hanna"; "Dana Leak"; "Triston Rigsby"; "Cyril Strong"; "Cyril Strong"; "Corydon Keck"; "Hija Hanna"; "Kason Leonard"; "Lewis Moya"; "Kason Leonard"; "Lewis Moya"; "Jaden Bonds"; "Doloris Hawley"}, 1)

and returns "Simeon Fernandes" in cell C21.

### Filter unique distinct values from three related tables

There are two records of Salesperson "Simeon Fernandes" and Company "Sample Company" in the last table. Filtered Salespersons in cell range C21:C25 return Simeon Fernandes only once.

Array formula in cell range C21:

How to create an array formula

### Filter duplicates from three related tables

**Array formula in cell C21:**

How to create an array formula

### Sum values from three related tables

**Array formula in cell C21:**

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

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

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

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

### 10 Responses to “Working with three related 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

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form

Its possible for me to get the regular updates from your site. Your site is excellent I ever seen.

Many Thanks

Chandra

shekher.vish@gmail.com

Chandra,

thank you for your kind words!

You can subscribe to my blog updates by email or rss, see the right sidebar of this page.

[...] 0)), "", INDIRECT("Table2[Company]")), 0)))=FALSEYou can find the array formula in cell C23 here:Lookups in three related tables and return multiple valuesDownload excel *.xlsx fileConditional formatting in related tables.xlsxRelated posts:Working with [...]

Oscar,

You are genius as your name.

Can you suggest me about site or vba book for vba excel as i did not found much about vba at your site get-digital-hel.

vijay singh

VIJAY SINGH,

I recommend "Excel 20xx Power Programming with VBA", author John Walkenbach.

Hi Oscar, your website has always come to my mind each time when I'm having difficulty with my Excel skill and I've learn alot from your post and thanks you very much for creating this for all of us to learn. I'm doing a reporting which need a similar function like what you has demostrate to us but I'll have 3 tab sheet for a product family name G1, G2 & G3 (which is manage by individual) plus a summary tab sheet.

Each of the product family tab sheet will have a shipment breakdown it consist of a schedule for shipment in the mode of Air, Ocean, Land. So how can I by choosing the date (which is the drop down list) it will show all the product in all the (G1, G2 & G3) which I intend to do it in the Summary tab sheet.

Once again, thanks for your help. Please let me know how can I upload my spreadsheet for your to understand more.

Best Regards,

Patrick

Patrick,

you can upload your workbook here: Upload

Hi,

I am looking for a inventory database. Can you help me in this. I am looking for excel tables to have sales data, purchase data and stock data date-wise.

Santosh,

You have email.

[…] Working with three related tables […]