Finding matches between tables with columns and rows transposed

Posted on 2011-10-14
Last Modified: 2012-08-14
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.

Question by:gabrielPennyback
    LVL 41

    Accepted Solution

    Put this formula in cell E19, then copy down and across:


    See attached...

    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


    LVL 50

    Assisted Solution

    by:barry houdini
    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


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


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

    See attached

    regards, barry
    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 :)

    LVL 50

    Expert Comment

    by:barry houdini
    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


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


    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


    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
    LVL 50

    Expert Comment

    by:barry houdini
    This link (Example 2) is an example of the method I suggested

    LVL 41

    Expert Comment

    You go, barry!

    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    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…
    This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now