CODEWITHSUNDEEP
excellookup
topstuff
topstuff

Reputation: 129

Excel - Lookup between three tables

enter image description here

I am using Excel 2010. I have three tables: Code, Name and Region.
I would like to populate Region table with the values you can see. I would like to count the number of instances where Region appears in the Name Table, and what code is attributed to the name in the Code table.
I understand a lookup column on the Name Table with the Code would be sensible idea however, I'm curious as to whether this could be done without.
Thanks in advance for reading.

Upvotes: 0

Views: 49

Answers (2)

Scott Craner
Scott Craner

Reputation: 152465

use this:

=SUMPRODUCT(COUNTIFS(Code[[Name]:[Name]],Name[[Name]:[Name]],Code[[Code]:[Code]],Region[[#Headers],[A]])*(Name[[Region]:[Region]]=Region[@[Region]:[Region]]))

enter image description here

Upvotes: 1

DSteman
DSteman

Reputation: 1658

Assuming the left top cell in your picture is A1, the following formula should be pasted in H3 and can be copied pasted in the third table.

=COUNTIFS($B:$B;H$1;$E:$E;$G2)

Upvotes: 0

Related Questions