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

#### Dustinkli

##### Board Regular
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:
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
Hi,

You can try using SUMPRODUCT:

Book3.xlsx
ABCD
1Year202020192018
2Revenue321843213632765
3Revenue Growth0.0015-0.01920.035
4Cost of Revenue166051713616682
5Gross Profit155791500016083
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
7Research & Development1,8781,9111,821
Data1

Remove the number 1 in Data1! in my formula.

#### Fluff

##### MrExcel MVP, Moderator
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

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
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
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.

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.

### Which adblocker are you using?

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

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