## Working with three relational tables

I will in this article demonstrate four formulas that do lookups, extract unique distinct and duplicate values and sums numbers across three relational data sets using Excel formulas.

The image above shows a search value in cell C19, it is used as a search condition in column B and a matching value is found in cell B8. The corresponding value in column C is cell C8 which is then used do a second lookup in another table next to the first table.

The value in cell C8 is found in column E four times, they are E4, E8, E12 and E15. The formula now uses the values on the same row in column F to do a third lookup in column H.

Six matches are found in column H and the values on the same rows from colmn I are returned to cell range C21:C26.

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

**Table of Contents**

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

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

The animated picture above shows you how the array formula works, itÂ 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
- Press with left mouse button on 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**

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

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

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

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

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

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

*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

### Related tables category

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

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

Excel 2010 has a PowerPivot feature and DAX formulas that let you work with multiple tables of data. You can […]

### Excel categories

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

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

Many Thanks

Chandra

[email protected]

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 values Get the 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 […]