Receiving "False" result from nested formula with multiple conditions

WirelessJoe

New Member
Joined
Jan 7, 2021
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
Hello, I'm using the following formula to select a pay curve based on two conditions: a job title (selected through data validation dropdown in cell C6) and whether or not their YTD and QTD revenue is over 100% (cells C52 and C46) If the title is "Job1" it should select from the pay curve in column B, if the title is "Job2" it should select from column C.

=IFERROR(IF($C$6="Job1",IF(AND(C52<100%,C46>100%),C46,INDEX('Pay Curves'!$B:$B,MATCH(C46,'Pay Curves'!$A:$A,0))),IF($C$6="Job2",IF(AND(C52<100%,C46>100%),C46,INDEX('Pay Curves'!$C:$C,MATCH(C46,'Pay Curves'!$A:$A,0))))),"")

For the two job titles in the formula, this works fine. However, I have a several other titles (None, Job3, etc.) that, when selected in cell C6, gives me a "False" result. I was trying, with the IFERROR, to simply have it return nothing "". How can I return a blank or zero?

Thanks in advance for any insight you might be able to provide.
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

JamesCanale

Board Regular
Joined
Jan 13, 2021
Messages
159
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Really hard to follow:

Excel Formula:
=IF($C$6="Job1",IF(AND(C52<100%,C46>100%),C46,INDEX('Pay Curves'!$B:$B,MATCH(C46,'Pay Curves'!$A:$A,0))),IF($C$6="Job2",IF(AND(C52<100%,C46>100%),C46,INDEX('Pay Curves'!$C:$C,MATCH(C46,'Pay Curves'!$A:$A,0))),""))

You missed that ,"" near the end on a dangling false condition.

Basically, if you write:

Excel Formula:
=if(4=3,"")

it will return FALSE because you didn't put that last parameter in.
 
Solution

WirelessJoe

New Member
Joined
Jan 7, 2021
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
Thank you for fixing this, it does work now. I'm relatively new to more complex formulas, and appreciate the time you took to help.
 

JamesCanale

Board Regular
Joined
Jan 13, 2021
Messages
159
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
You're welcome.

I love a puzzle.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,478
Messages
5,636,567
Members
416,924
Latest member
cmlacerna

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

Which adblocker are you using?

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
Top