[Solved] Nested IF AND functions using Named Ranges

[drbiggs]

Greetings excel gurus!

I'm having trouble getting Excel to return expected results. I must be using the wrong syntax, and can't figure it out.

Named range 1: Fiscal_Yr; rows A1-A1000 and the values in that range use data validation list ranging from 16 to 40.
Named range 2: In_Plan; rows B1-B1000 and the values in that range use data validation list, only with "Y" or "N" as allowable values.

I try to use the following formulas, and they don't work:
=IF(AND(Fiscal_Yr=16,In_Plan="Y"),"Re-Validate","Valid") ... cell A1 clearly set to 16, cell B1 clearly set to Y, and it returns as FALSE or "Valid"

=IF(AND(Fiscal_Yr="16",In_Plan="Y"),"Re-Validate","Valid") ... cell A1 clearly set to 16, cell B1 clearly set to Y, and it returns as FALSE or "Valid" ... for this one I've tried to make sure the format of column A is number and B is Text or General

=IF(AND(Fiscal_Yr,"=16",In_Plan,"=Y"),"Re-Validate","Valid") ... with cell A1 clearly set to 17, cell B1 clearly set to Y, and it returns as TRUE or "Re-Validate" ... for this one I think the comma after the named range is the problem, I'm surprised a value is returned at all

Seems the only way I can get the functionality intended is to use cell references in the formula to make the function return intended results.

=IF(AND(A1=16,B1="Y"),"Re-Validate","Valid") ... cell A1 clearly set to 16, cell B1 clearly set to Y, and it returns as TRUE or "Re-Validate"

 

[Lutfor Rahman Shimanto]

Greetings excel gurus!

I'm having trouble getting Excel to return expected results. I must be using the wrong syntax, and can't figure it out.

Named range 1: Fiscal_Yr; rows A1-A1000 and the values in that range use data validation list ranging from 16 to 40.
Named range 2: In_Plan; rows B1-B1000 and the values in that range use data validation list, only with "Y" or "N" as allowable values.

I try to use the following formulas, and they don't work:
=IF(AND(Fiscal_Yr=16,In_Plan="Y"),"Re-Validate","Valid") ... cell A1 clearly set to 16, cell B1 clearly set to Y, and it returns as FALSE or "Valid"

=IF(AND(Fiscal_Yr="16",In_Plan="Y"),"Re-Validate","Valid") ... cell A1 clearly set to 16, cell B1 clearly set to Y, and it returns as FALSE or "Valid" ... for this one I've tried to make sure the format of column A is number and B is Text or General

=IF(AND(Fiscal_Yr,"=16",In_Plan,"=Y"),"Re-Validate","Valid") ... with cell A1 clearly set to 17, cell B1 clearly set to Y, and it returns as TRUE or "Re-Validate" ... for this one I think the comma after the named range is the problem, I'm surprised a value is returned at all

Seems the only way I can get the functionality intended is to use cell references in the formula to make the function return intended results.

=IF(AND(A1=16,B1="Y"),"Re-Validate","Valid") ... cell A1 clearly set to 16, cell B1 clearly set to Y, and it returns as TRUE or "Re-Validate"

Hello Drbiggs

Thanks for reaching out and sharing your problem with such clarity. I have demonstrated your situation in a solution workbook. I think using the cell references instead of named ranges is better.

However, if you need to use named ranges, I have enhanced the formula:

=IF(AND(INDEX(Fiscal_Yr, ROW()) = 16, INDEX(In_Plan, ROW()) = "Y"), "Re-Validate", "Valid")

​

I have attached the solution workbook for better understanding. I hope the idea will help you; good luck.

Regards
Lutfor Rahman Shimanto
ExcelDemy

 

[drbiggs]

Thanks SOO Much!