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