# Excel If function not functioning if reference a named range

#### bisel

##### Board Regular
I apologize if this has been asked before, but I cannot find a relevant post.

I have a simple IF function where I compare a value of a cell to a named range (also a single cell). Like this ...

=IF(AM\$268 > display_years,TRUE,FALSE) ... display_years is the named range that is a single cell.

In the case above, the named range, "display_years" = 15 and the value of cell AM\$268 = 2047, but the result of the logical test is FALSE.

If I remove all references to a named range, the logical test will work fine. For example,

=IF(AM\$268 > \$AQ\$264,TRUE,FALSE) ... where \$AQ\$264 = 15, the result of the logical test is TRUE

Can some one explain why this happening?

Thanks,

Steve

### Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

#### footoo

##### Well-known Member
Are you sure that "display_years" refers to AQ264 ?

#### bisel

##### Board Regular
No it doesn't. Display_years refers to another cell on a different sheet

steve

#### mikerickson

##### MrExcel MVP
Is it possible that AM268 and/or display_years are string values?

What do =ISNUMBER(AM268) and =ISNUMBER(display_years) return?

What is the definition of display_years? If it a reference to a cell with a formula, what is that formula?

Last edited:

#### bisel

##### Board Regular
I have found a work around and it may be the definitive answer. The cell that the named range was referring to in the IF statement (function) is part of a range of cells that are referenced in another, unrelated function, that is a LOOKUP function. If I create the IF statement using a named range that is not part of the range referenced by the LOOKUP function, it works just fine. Ultimately, that is what I have done to resolve this problem. It seems odd that this works, but one of many odd things that I have found that occur in Excel.

Last edited:

#### Aladin Akyurek

##### MrExcel MVP
I have found a work around and it may be the definitive answer. The cell that the named range was referring to in the IF statement (function) is part of a range of cells that are referenced in another, unrelated function, that is a LOOKUP function. If I create the IF statement using a named range that is not part of the range referenced by the LOOKUP function, it works just fine. Ultimately, that is what I have done to resolve this problem. It seems odd that this works, but one of many odd things that I have found that occur in Excel.

"Odd things" should be exposed, if they are just that, "odd things".

What does the following do?

What do you get with the following?

=COUNT(display_years)

=COUNTA(display_years)

Replies
3
Views
175
Replies
1
Views
220
Replies
5
Views
281
Replies
3
Views
600
Replies
2
Views
424

Threads
1,191,197
Messages
5,985,231
Members
439,952
Latest member
djharter

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

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