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

Dustinkli

Board Regular
Joined
Mar 26, 2019
Messages
54
Office Version
  1. 365
  2. 2011
Platform
  1. Windows
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:

Year202020192018
Revenue
Revenue Growth
Cost of Revenue
Gross Profit
Selling, General & Admin
Research & Development


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

Year202020192018
Revenue32,18432,13632,765
Revenue Growth0.15%-1.92%3.50%
Cost of Revenue16,60517,13616,682
Gross Profit15,57915,00016,083
Selling, General & Admin6,9297,0297,602
Research & Development1,8781,9111,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:
Excel Formula:
=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.
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
Hi,

You can try using SUMPRODUCT:

Book3.xlsx
ABCD
1Year202020192018
2Revenue321843213632765
3Revenue Growth0.0015-0.01920.035
4Cost of Revenue166051713616682
5Gross Profit155791500016083
6Selling, General & Admin692970297602
7Research & Development187819111821
Sheet782
Cell Formulas
RangeFormula
B2:D7B2=SUMPRODUCT((Data1!$A$2:$A$7=$A2)*(Data1!$B$1:$D$1=B$1)*Data1!$B$2:$D$7)


Book3.xlsx
ABCD
1Year202020192018
2Revenue32,18432,13632,765
3Revenue Growth0.15%-1.92%3.50%
4Cost of Revenue16,60517,13616,682
5Gross Profit15,57915,00016,083
6Selling, General & Admin6,9297,0297,602
7Research & Development1,8781,9111,821
Data1


Remove the number 1 in Data1! in my formula.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
60,211
Office Version
  1. 365
Platform
  1. Windows
How about
Excel Formula:
=INDEX(Data!$B$2:$E$1000,MATCH($A2, Data!$A$2:$A$1000,0),MATCH(B$1,Data!$B$2:$E$2,0))
 

Dustinkli

Board Regular
Joined
Mar 26, 2019
Messages
54
Office Version
  1. 365
  2. 2011
Platform
  1. Windows

ADVERTISEMENT

How about
Excel Formula:
=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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
60,211
Office Version
  1. 365
Platform
  1. Windows
Oops, had the wrong range for the headers.
Excel Formula:
=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

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,136,952
Messages
5,678,745
Members
419,782
Latest member
gc75150

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top