• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 199
  • Last Modified:

Finding matches between tables with columns and rows transposed

I have two tables:

Table 1 has serial numbers across the top, and dates down the leftmost column
Table 2 has dates across the top, and serial numbers down the leftmost column

I need formulas in Table 2 that will look for matches in the 'transposed' intersections in Table 1.

Please look at the attached workbook to see what I mean. The solution needs to work in Excel 2003 as well as 2007.

Thanks,
John
Find-Intersections.xlsx
0
John Carney
Asked:
John Carney
  • 4
  • 3
2 Solutions
 
dlmilleCommented:
Put this formula in cell E19, then copy down and across:

=SUMPRODUCT(($C$3:$C$14=E$18)*($D$2:$F$2=$C19)*($D$3:$F$14))

See attached...

Dave
Find-Intersections-1-.xlsx
0
 
dlmilleCommented:
To explain, the first parameter:

($C$3:$C$14=E$18) locks in a ROW based on the month

The second Parameter:

($D$2:$F$2=$C19) locks in the COLUMN based on the serial number

The third Parameter:

($D$3:$F$14) is the data to sum, based on the locked ROW and COLUMN intersection

Cheers,

Dave
0
 
barry houdiniCommented:
Hello John,

Normally to return a single value it's best to use a "lookup" type function in my opinion. My preference would be this formula in E19 copied across and down

=INDEX($D$3:$F$14,MATCH(E$18,$C$3:$C$14,0),MATCH($C19,$D$2:$F$2,0))

or you could use HLOOKUP or VLOOKUP if you want it shorter, i.e.

=HLOOKUP($C19,$D$2:$F$14,MATCH(E$18,$C$3:$C$14,0)+1,0)

These will both work with either numeric or text data unlike Dave's SUMPRODUCT suggestion

See attached

regards, barry
27397976.xls
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
dlmilleCommented:
IMHO - The sumproduct is easier to understand, espeically when doing multiple backflips to find an intersection, rather than pull out INDEX, MATCH, and H/VLOOKUPS.  The data is numeric, ergo :)

However, its good for all of us to know multiple ways to skin the cat, as the knife can get rusty :)

0
 
barry houdiniCommented:
Hello Dave/John,

Of course both methods are valid here and Dave's SUMPRODUCT method would be the better option if there could be multiple matches in either row or column headers (with the aim to sum all the intersection values for those matches).

I'd take issue with your comment about understanding, though. I think the concept of the INDEX/MATCH/MATCH setup is quite simple.

INDEX works like this

=INDEX(Area,x,y)

will give you the value at the intersection of the xth row and the yth column of Area, so, for example, if you have

=INDEX(D3:F7,3,2)

that will give you the value from the 3rd row of D3:F7 (row 5) and the 2nd column (column E) so it gives the value from E5

In the above formula the x and y values are provided by MATCH.  That function finds the position of the first instance of a value in a vector (a single column or row). So if you use

=MATCH("abc",F3:J3,0)

and abc is found first in H3 then the formula returns 3 because H3 is the 3rd cell in the F3:J3 row

Of course it doesn't make much difference in a small table but INDEX/MATCH/MATCH would also be more efficient than multiplying matrices/arrays in SUMPRODUCT

regards, barry
0
 
barry houdiniCommented:
This link (Example 2) is an example of the method I suggested

barry
0
 
dlmilleCommented:
You go, barry!

:)
0
 
John CarneyReliability Business Tools Analyst IIAuthor Commented:
You guys are amazing, thanks! Dave's answer is perfect since we're looking for numbers in this case.

But of course Barry's answer accommodates text as well. It took me a while to understand it, but if I have it right its logic simply finds the correct x and y values, i.e. finds the intersection.

Thank you both.

- John
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now