Using "Index" to lookup a cell based on 2 criteria

[Dustinkli]

I'm currently working with financial data and I'm trying to use Index to lookup stock information on another sheet. Right now I have the below on my active sheet:

Year	2020	2019	2018
Revenue
Revenue Growth
Cost of Revenue
Gross Profit
Selling, General & Admin
Research & Development

and on another sheet called "Data" I have the following:

Year	2020	2019	2018
Revenue	32,184	32,136	32,765
Revenue Growth	0.15%	-1.92%	3.50%
Cost of Revenue	16,605	17,136	16,682
Gross Profit	15,579	15,000	16,083
Selling, General & Admin	6,929	7,029	7,602
Research & Development	1,878	1,911	1,821

What I'm trying to do is reference the information in "Data" in my active sheet with a formula by referencing both Column A and Row 1 (i.e. in the case of cell B2 I'd reference "Revenue" and "2020" to lookup that value in the Data sheet.


Here a the formula that works:

=IFERROR(INDEX(Data!B:B,AGGREGATE(15,6,(ROW(Data!$A:$A)-ROW($B$1)+1)/(A:A=A2),ROWS($B$1))),"")

But I wonder if there's an easier way to do this. Is it possible to do this using a simpler function? I also keep getting errors that there's a circular reference in the calculation but I'm not sure why I'm getting that error.

 

[jtakw]

Hi,

You can try using SUMPRODUCT:

Book3.xlsx
	A	B	C	D
1	Year	2020	2019	2018
2	Revenue	32184	32136	32765
3	Revenue Growth	0.0015	-0.0192	0.035
4	Cost of Revenue	16605	17136	16682
5	Gross Profit	15579	15000	16083
6	Selling, General & Admin	6929	7029	7602
7	Research & Development	1878	1911	1821
Sheet782

Cell Formulas
Range	Formula
B2:D7	B2	=SUMPRODUCT((Data1!$A$2:$A$7=$A2)*(Data1!$B$1:$D$1=B$1)*Data1!$B$2:$D$7)

Book3.xlsx
	A	B	C	D
1	Year	2020	2019	2018
2	Revenue	32,184	32,136	32,765
3	Revenue Growth	0.15%	-1.92%	3.50%
4	Cost of Revenue	16,605	17,136	16,682
5	Gross Profit	15,579	15,000	16,083
6	Selling, General & Admin	6,929	7,029	7,602
7	Research & Development	1,878	1,911	1,821
Data1

Remove the number 1 in Data1! in my formula.

 

[kweaver]

What about:

Book2
	A	B	C	D
1	Year	2020	2019	2018
2	Revenue	32,184	32,136	32,765
3	Revenue Growth	0.15%	-1.92%	3.50%
4	Cost of Revenue	16,605	17,136	16,682
5	Gross Profit	15,579	15,000	16,083
6	Selling, General & Admin	6,929	7,029	7,602
7	Research & Development	1,878	1,911	1,821
Active

Cell Formulas
Range	Formula
B2:D7	B2	=VLOOKUP($A2,Data!$A$1:$D$7,MATCH(B$1,Data!$A$1:$D$1,0),FALSE)

 

[Fluff]

How about

=INDEX(Data!$B$2:$E$1000,MATCH($A2, Data!$A$2:$A$1000,0),MATCH(B$1,Data!$B$2:$E$2,0))

 

[Dustinkli]

How about

=INDEX(Data!$B$2:$E$1000,MATCH($A2, Data!$A$2:$A$1000,0),MATCH(B$1,Data!$B$2:$E$2,0))

Jtakw's and Kweaver's worked but this one did not.


Also, what is the difference between the VLookup & Match and the SUMPRODUCT functions and why do they seem to do the same thing?

 

[Fluff]

Oops, had the wrong range for the headers.

=INDEX(Data!$B$2:$E$1000,MATCH($A2,Data!$A$2:$A$1000,0),MATCH(B$1,Data!$B$1:$E$1,0))

The index/match/match & Vlookup/match are much the same, although the index version will be quicker over large data.
The sumproduct is effectively summing everything for Revenue & 2020. As long as you only have one value that matches both criteria it works.

 

[jtakw]

One more bit about the differences in the functions...
I believe INDEX/MATCH, and VLOOKUP returns only 1 match (first match if using exact match), and as Fluff said
SUMPRODUCT will SUM all matches and return the total, so if you Do have more than 1 line of "Revenue" for say year 2020, it'll return the total for that year.