Multiple Column Lookup - Index, Match functions

[Ravi]

Hi All,

I have an interesting predicament in using lookup, index and match functions. I have tried this for some time and was not going anywhere so I thought I would leave this to the experts on this forum.

I need to have use both two-way and two-column lookups to come up with the needed output and haven't been able crack this puzzle. Here is an example of my predicament:

Assuming that my data sheet has the following data - ofcourse the real data actually runs close to 5000 rows and across 15 columns.

Company Metric Qtr1 Qtr2 Qtr3 Qtr4 FY
A Revenue 1 2 3 4 5
A Gross Profit 3 5 4 2 1
A Net Profit 8 2 4 5 6
B Revenue 9 3 2 0 1
B Gross Profit
B Net Profit
C Revenue
C Gross Profit 2 0 5 0 2
C Net Profit
D Revenue
D Gross Profit
D Net Profit


I would like to create a function which will give me the values for four quarters and the year when i key in a particular company's name and the metric.

For example if I type in C in a cell in Column A and Gross Profit in Column B - then the formulas in Column C to Column G should result in the values "2 0 5 0 2" based on values in Columns A and B.

Hope I am clear in explaining my requirements. Appreciate all your help.

Thanks in Advance.

Ravi

 

[Bob Umlas]

You can get the row you want by ctrl/shift/entering this (Assumes your
Company code is in J1 and Metric in J2, for example):
=MATCH(J1&J2,A1:A5000&B1:B5000,0)
This number can then be used as an index to get the rest of the data. For
example, if this formula were in J3, you can use
=INDEX(C:C,$J$3) and fill right to get the other #s.

Bob Umlas
Excel MVP

Hi All,

I have an interesting predicament in using lookup, index and match

functions. I have tried this for some time and was not going anywhere so I
thought I would leave this to the experts on this forum.

I need to have use both two-way and two-column lookups to come up with the

needed output and haven't been able crack this puzzle. Here is an example of
my predicament:

Assuming that my data sheet has the following data - ofcourse the real

data actually runs close to 5000 rows and across 15 columns.

Company Metric Qtr1 Qtr2 Qtr3 Qtr4 FY
A Revenue 1 2 3 4 5
A Gross Profit 3 5 4 2 1
A Net Profit 8 2 4 5 6
B Revenue 9 3 2 0 1
B Gross Profit
B Net Profit
C Revenue
C Gross Profit 2 0 5 0 2
C Net Profit
D Revenue
D Gross Profit
D Net Profit


I would like to create a function which will give me the values for four

quarters and the year when i key in a particular company's name and the
metric.

For example if I type in C in a cell in Column A and Gross Profit in

Column B - then the formulas in Column C to Column G should result in the
values "2 0 5 0 2" based on values in Columns A and B.