Solved

# Finding matches between tables with columns and rows transposed

Posted on 2011-10-14
185 Views
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
Question by:gabrielPennyback

LVL 41

Accepted Solution

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

LVL 41

Expert Comment

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

LVL 50

Assisted Solution

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

LVL 41

Expert Comment

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

LVL 50

Expert Comment

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

LVL 50

Expert Comment

This link (Example 2) is an example of the method I suggested

barry
0

LVL 41

Expert Comment

You go, barry!

:)
0

LVL 1

Author Closing Comment

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

## Featured Post

### Suggested Solutions

Sometimes we don't want to show zeros in our Excel spreadsheets. This is sometimes most evident in our charts. Look at the chart below, all the zero values are visible. I think that all will agree with the fact that zero values are not looking nice …
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…