# Looking for IF Statement formula

#### DarrenF

##### Board Regular
I have a complex question here:

I have a drop down that will produce 2 different types of information.

Legacy Scorecard
X-1 Scorecard

I have the following IF statement

=IF(\$G\$1="Legacy Scorecard",IF(E5=" ","",IF(E5=0,"E",IF(E5>Metrics!F4,"U",IF(E5>Metrics!E4,"NI",IF(E5>Metrics!D4,"AE",IF(E5>Metrics!C4,"HE",IF(E5<=Metrics!C4,"E"))))))),IF(\$G\$1,"X-1 Scorecard",IF(E5=" ","",IF(E5=0,"E",IF(E5>Metrics!M4,"U",IF(E5>Metrics!L4,"NI",IF(E5>Metrics!K4,"AE",IF(E5>Metrics!J4,"HE",IF(E5<=Metrics!J4,"E")))))))))

I know its very lengthy and probably quite messy. I'll try to explain what I'm trying to do.

I have 2 different target goals depending on if your Legacy or X-1 scoring. I'm also trying to decide weither your falling in the catagory of U, NI, AE, HE or E.

Let me know if I need to provide further information.

### Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

#### Steph77

##### Board Regular
You have a problem with the last IF statement within each section:

IF(E5<=Metrics!C4,"E") and IF(E5<=Metrics!J4,"E")

The IF needs a value if true and a value if false, these two bits of the formula are only giving a value if true.

Try replacing the last 2 IFs that lead to them with something like:

IF(E5>Metrics!C4,"HE","E") and IF(E5>Metrics!J4,"HE","E")

(Remember to take one of the close brackets out at the end of the section too or the number of open and close brackets won't match)

#### DarrenF

##### Board Regular
Thank you. When I go to X-1 Scorecard info I end up getting #VALUE!

#### Steph77

##### Board Regular
Normally a #VALUE error means there's something that looks like a number to you but it's actually being treated by Excel as a text string. Check any of the cells that have numbers in them are set to a number format as the first way to try and fix that.

I also think the same problem as I mentioned before is affecting the second half of your formula

IF(\$G\$1,"X-1 Scorecard",**STUFF**)

If you can only pick between two values then take out this bit of the IF chain and skip straight to the other stuff, as long as if it isn't Legacy it will ALWAYS be X-1 then that might solve it too...

Edit so:

=IF(\$G\$1="Legacy Scorecard",IF(E5=" ","",IF(E5=0,"E",IF(E5>Metrics!F4,"U",IF(E5>Metrics!E4,"NI",IF(E5>Metrics!D4,"AE",IF(E5>Metrics!C4,"HE","E")))))),IF(E5=" ","",IF(E5=0,"E",IF(E5>Metrics!M4,"U",IF(E5>Metrics!L4,"NI",IF(E5>Metrics!K4,"AE",IF(E5>Metrics!J4,"HE","E")))))))

Last edited:

#### DarrenF

##### Board Regular

I appologize, I'm not understanding the 2nd solution.

#### taurean

##### Well-known Member
If G1 is always going to be either of the two then you can try like below:
=IF(\$G\$1="Legacy Scorecard",IF(E5="","",IFERROR(LOOKUP(E5,(Metrics!C4:F4)+1,{"HE","AE","NI","U"}),"E")),IF(E5="","",IFERROR(LOOKUP(E5,(Metrics!J4:M4)+1,{"HE","AE","NI","U"}),"E")))

#### xrichardx

##### Board Regular
=IF(\$G\$1="Legacy Scorecard",IF(E5=" ","",IF(E5=0,"E",IF(E5>Metrics!F4,"U",IF(E5>Metrics!E4,"NI",IF(E5>Metrics!D4,"AE",IF(E5>Metrics!C4,"HE",IF(E5<=Metrics!C4,"E"))))))),IF(\$G\$1,"X-1 Scorecard",IF(E5=" ","",IF(E5=0,"E",IF(E5>Metrics!M4,"U",IF(E5>Metrics!L4,"NI",IF(E5>Metrics!K4,"AE",IF(E5>Metrics!J4,"HE",IF(E5<=Metrics!J4,"E")))))))))

First thing, you've got a typing mistake in the condition "\$G\$1,"X-1 Scorecard"", it shall be an "=" equal sign, "\$G\$1="X-1 Scorecard"".

I also re-arranged your code with the logic of "IF, ELSEIF, ELSEIF, ELSE, ENDIF", against your logic of "IF, ELSE, ELSEIF, ELSEIF, ENDIF".

Code:
``=IF(\$G\$1="Legacy Scorecard",IF(E5=0,"E",IF(E5>Metrics!F4,"U",IF(E5>Metrics!E4,"NI",IF(E5>Metrics!D4,"AE",IF(E5>Metrics!C4,"HE",IF(E5<=Metrics!C4,"E","")))))),IF(\$G\$1="X-1 Scorecard",IF(E5=0,"E",IF(E5>Metrics!M4,"U",IF(E5>Metrics!L4,"NI",IF(E5>Metrics!K4,"AE",IF(E5>Metrics!J4,"HE",IF(E5<=Metrics!J4,"E","")))))),""))``

But, the above code will still have a conflict depending on your values in "IF(E5=0,"E" and IF(E5<=Metrics!C4,"E"". Try to minimize it.

Cheers
Rich

#### ukmikeb

##### Well-known Member
If G1 is always going to be either of the two then you can try like below:
=IF(\$G\$1="Legacy Scorecard",IF(E5="","",IFERROR(LOOKUP(E5,(Metrics!C4:F4)+1,{"HE","AE","NI","U"}),"E")),IF(E5="","",IFERROR(LOOKUP(E5,(Metrics!J4:M4)+1,{"HE","AE","NI","U"}),"E")))

Or a slimmed down version :-
Code:
``=IF(E5="","",IFERROR(LOOKUP(E5,INDEX((Metrics!C4:F4,Metrics!J4:M4),,,IF(\$G\$1="Legacy Scorecard",1,2))+1,{"HE","AE","NI","U"}),"E"))``

hth

Replies
5
Views
137
Replies
0
Views
127
Replies
12
Views
424
Replies
21
Views
502
Replies
3
Views
255

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,487
Messages
5,831,975
Members
430,099
Latest member
rdhoy

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

### Which adblocker are you using?

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

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