#REF! Error in INDEX/MATCH Equation with Named Ranges

**shawnvon** · 01-24-2015, 03:01 PM

Hello, I am having a #REF error occur and I cannot tell why. The (frustrating) problem is that I am getting a #REF! error in one column of the “Database” tab that I can’t resolve and was wondering if you had any thoughts as to why. The equation is exactly the same as in the four columns to the left except that instead of referencing 2015E or 2016E it is referencing 2017E. And it’s not in all cells but the cells that don’t get a #REF! error aren’t calculating correctly either. When I F9 the various parts of the formulas I don’t get a #REF! error until I F9 the entire first INDEX equation. And when I F9 the equations that aren't #REF!-ing they show the correct numbers but not the correct result.

Please help.

Thanks in advance.

CF&T Market Model v3ShawnV.xlsx

**Philb1** · 01-24-2015, 07:28 PM

I noticed that you're referencing the cell the formula is in

Please Login or Register  to view this content.

When I changed it to

Please Login or Register  to view this content.

It worked

Does that help

**skywriter** · 01-24-2015, 07:59 PM

Hi shawnvon,

Regarding the formula in database cell L5, specifically the first Index function. The second match is returning a 5 for the column number, but the named range you are indexing only has 4 columns. I think in your match function you really want Assumptions!$C$6:$F$6,0).

Hope that helps.