• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Index Match by matching column and row together

Peter Bartholomew

Well-Known Member
The odd thing about this question is that, despite the use of INDEX/MATCH or VLOOKUP, the required data columns are actually returned in their entirety. Thus
= CompanyName

return the 1st and 3rd columns of the output table. This can be as
1. Implicit intersection - align the rows of the output table with the input
2. A CSE array - commit each column with Ctrl+Shift+Enter
3. A dynamic array - Enter a formula in the top cell of each range only (beta-release version of Excel)
The second column is the only place where any form of lookup is essential. My suggestion is to look up the column header in its validation list
= CHOOSE( MATCH( Measure, ValidationList, 0 ), RevenueGrowth, EBITDA, DebtRatio )