Index Match by matching column and row together

[msharma864512]

Hi,

I am trying to pull financial metrics in cell C2 (Sheet 7) by matching it through Row 3 (Sheet 1) along with matching the companies in sheet 7 with that of companies in sheet 1.

Regards,
Manish

 

[vletm]

msharma864512
for cell C3 =VLOOKUP(B3,Sheet1!B:F,5,FALSE)
for cell D3 =VLOOKUP(B3,Sheet1!B:Q,16,FALSE)
and copy down

 

[msharma864512]

msharma864512
for cell C3 =VLOOKUP(B3,Sheet1!B:F,5,FALSE)
for cell D3 =VLOOKUP(B3,Sheet1!B:Q,16,FALSE)
and copy down

Appologies for the late reply,

Thank you for the help, it really solved my query.

Regards,
Manish

 

[Peter Bartholomew]

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
= TSR
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 )