[Solved] ALWAYS GETTINF EITHR #VALUE OR #N/A ERROR IN MY FORMULA

[Vera]

Hi Rahman hope you're doing fine !
i need your help with the below ,my formula in not working can you figure it out why ? my problem is in cells B 15, B 16, B 17 and B 18 and these cells are data validation from the table below can you help pls? thank you in advance

 

[Lutfor Rahman Shimanto]

Hi Rahman hope you're doing fine !
i need your help with the below ,my formula in not working can you figure it out why ? my problem is in cells B 15, B 16, B 17 and B 18 and these cells are data validation from the table below can you help pls? thank you in advance

Dear Vera

Welcome to ExcelDemy Forum! I am doing fine, thanks for asking. You have mentioned the formula you are working with now always returns a #N/A error. The reason behind the #N/A error is the wrong placement of comparing Year with the values of D22:G22 ($B$15=$D$22:$G$22).

I am delighted to inform you that I have overcome this issue by modifying your formula.

Follow these:

Step 1: Select cell B19 => Insert the following formula.

=INDEX($D$23:$G$26, MATCH(1, ($B$16=$C$23:$C$26)*($B$18=$B$23:$B$26)*($B$17=$A$23:$A$26), 0), MATCH($B$15, $D$22:$G$22,0 ))

​

Step 2: Press Enter => You will see 2.00% in cell B19.

​

OUTPUT: I have applied the Data Validation within the input cells like the following GIF:

​

I am also attaching the solution workbook for better understanding. Stay blessed.

Regards
Lutfor Rahman Shimanto
ExcelDemy Team

 

[Vera]

Dear Rahman

Thank you so much for helping, it worked 100%
Thks again
Vera

 

[Lutfor Rahman Shimanto]

Dear Rahman

Thank you so much for helping, it worked 100%
Thks again
Vera

Dear Vera

You are most welcome. I am pleased to hear that it worked perfectly.

If you get stuck with more exciting problems like this, do not hesitate to reach ExcelDemy Forum. Stay Blessed.

Regards
Lutfor Rahman Shimanto
ExcelDemy Team