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.
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
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 :)
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
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.
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.
=SUMPRODUCT(($C$3:$C$14=E$
See attached...
Dave
Find-Intersections-1-.xlsx