Can I use a range name in if formula?

[Closed]
Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Hello,

I have a workbook with two sheets, Data and Transactions List. I have named cells A4:A7 on the second sheet as Income (Dividend, Interest, Cash, Tax).

I would like a formula in column F of the Data sheet that leaves it blank unless column C contains one of the entries in the Income range, in which case it should be the column J value.

I've tried
=IF(C384=Income,J384,"")
but it returns #Value!

Can anyone help?
Many Thanks


1 reply

Posts
2805
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 14, 2021
481
Hi CS Formula,

You can't compare a single cell to a range when using IF formula.

Try this: =IF(OR(C384=A4,C384=A5,C384=A6,C384=A7),J384,"")

Best regards,
Trowa
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
One alternate method would be to use COUNTIF
=IF(COUNTIF(A4:a47,"Income")>0,J384,"")