Forum Discussion
data tables & index/match function
As of yesterday, when I create a data table within a Excel 365 file & use that table within an index/match function, when I hit enter, the formula is looking for an external source & never had to do ...
well, this formula results in a spill error:
=INDEX('kpi Database'!$C$2:$E$17,MATCH(1,'kpi Database'!$A$2:$A$17=$A$7)*('kpi Database'!$B$2:$B$17=A$5)*('kpi Database'!$C$1:$E$1=$B$5),0)
is there some reason why the formula won't read the years in KPI database (in the rows C1:E1) vs. everything else is in columnar form. I would think the formula should be able to find it & match it. maybe I need to include another index so it reads the rows? The resulting value should be 250 for "CDF Vol for 2019". Sorry this is causing such agita, but I am been working on this for couple of hours.
=INDEX('kpi Database'!$C$2:$E$17,MATCH(1,'kpi Database'!$A$2:$A$17=$A$7)*('kpi Database'!$B$2:$B$17=A$5)*('kpi Database'!$C$1:$E$1=$B$5),0)
is there some reason why the formula won't read the years in KPI database (in the rows C1:E1) vs. everything else is in columnar form. I would think the formula should be able to find it & match it. maybe I need to include another index so it reads the rows? The resulting value should be 250 for "CDF Vol for 2019". Sorry this is causing such agita, but I am been working on this for couple of hours.
Did you take the trouble to look at the workbook that I attached?
- I apologize, I did not see that attachment, I was too focused on my spreadsheet. I hadn't seen that way to create a formula for the rows. Thank you for your time.