IF AND Functions coming back as False

=IF(AND($K$4>=1,$K$4<=4),"+2",IF(AND($K$4>="5",$K$4<="8"),"+3",IF(AND($K$4>="9",$K$4<="12"),"+4",IF(AND($K$4>=13,$K$4<=16),"+5",IF(AND($K$4>="Lvl 17",$K$4<="Lvl 20"),"+6")))))

I have that function, It was showing FALSE but when I put the "" around the numbers it worked except IF(AND($K$4>="9",$K$4<="12") Can anyone explain why this one portion is showing false? Is it because it goes from single digit to double? If so, how do I fix that?

You are using quotation marks for numbers - this tells Excel to treat it as texts. As such, "9" and "12" has different sizes than the numeric 9 and 12. That is "12" is actually less than "9". I would recommend working with actual numeric figures instead, as you've down for numbers between 1 and 4. Removing all quotation marks around the numbers should resolve the issue.

Additionally, you are comparing "Lvl 17" and "Lvl 20" - this is inconsistent with the other comparison type where you only look at the numbers and do not include the text Lvl. Potential error here.

Also, the final IF function you have (Lvl 17 and Lvl 20) does not have a value_if_false component, and will result in FALSE if the number in K4 does not meet any of the requirements.

Lastly, looking at your formula, it seems like it can be simplified to a LOOKUP, i.e.

Formula:

=LOOKUP($K$4, {1,5,9,13,17}, {"+2", "+3", "+4", "+5", "+6"})

=IF(AND($K$4>=1,$K$4<=4),"+2",IF(AND($K$4>=5,$K$4<=8),"+3",IF(AND($K$4>=9,$K$4<=12),"+4",IF(AND($K$4>=13,$K$4<=16),"+5",IF(AND($K$4>=17,$K$4<=20),"+6","")))))

This is without quotations, however it now responds as a blank cell due to my "" for false, for some reason it is not seeing the numbers in the CELL, the CELL that is reference has a long IF AND function in it as well which is to calculate base on another cells input, unsure if this is a problem which I assume not.

As for your suggestion of the lookup function, yours returns N/A and I am not familiar enough in it to trouble shoot that. However it looks as though in the LOOKUP you are looking at a list of cells and it searches those cells for your query, well I need it checking the one cell and anything equal to and between 1-5 is to have 2 which as said would work with quotations around the numbers but won't work without quotations.

The #N/A error you have in your LOOKUP tells me that the cell in K4 is recognised as a text. Are you able to change the formula in K4 to be as below and see if it works.

Formula:

=VALUE(Long_If)

The LOOKUP function works this way
=LOOKUP(K4, RangeToFind, ResultingRange)

What it does is, look at K4, whatever number it maybe, and try and find it within RangeToFind. Once it finds a number (or the smaller closer number), it returns the corresponding number in ResultingRange.

=IF(AND($H$4>=0,$H$4<=299),"1",IF(AND($H$4>=300,$H$4<=899),"2",IF(AND($H$4>=900,$H$4<=2699),"3",IF(AND($H$4>=2700,$H$4<=6499),"4",IF(AND($H$4>=6500,$H$4<=13999),"5",IF(AND($H$4>=14000,$H$4<=22999),"6",IF(AND($H$4>=23000,$H$4<=33999),"7",IF(AND($H$4>=34000,$H$4<=47999),"8",IF(AND($H$4>=48000,$H$4<=63999),"9",IF(AND($H$4>=64000,$H$4<=84999),"10",IF(AND($H$4>=85000,$H$4<=99999),"11",IF(AND($H$4>=100000,$H$4<=119999),"12",IF(AND($H$4>=120000,$H$4<=139999),"13",IF(AND($H$4>=140000,$H$4<=164999),"14",IF(AND($H$4>=165000,$H$4<=194999),"15",IF(AND($H$4>=195000,$H$4<=224999),"16",IF(AND($H$4>=225000,$H$4<=264999),"17",IF(AND($H$4>=265000,$H$4<=304999),"18",IF(AND($H$4>=305000,$H$4<=354999),"19",IF(AND($H$4>=355000),"20"))))))))))))))))))))

This is my K4 cell..... I may have to try changing it in the morning.

Right, remove the quotation marks within "1", "2", "3", etc. That should fix it.

Also, looks like you can apply the same LOOKUP function here.

Formula:

=LOOKUP($H$4, {0;300;900;2700;6500;14000;23000;34000;48000;64000;85000;100000;120000;140000;165000;195000;225000;265000;305000;355000} , {1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20})

OR even shorter,

Formula:

=MATCH($H$4, {0;300;900;2700;6500;14000;23000;34000;48000;64000;85000;100000;120000;140000;165000;195000;225000;265000;305000;355000},1)

Thanks for the help, it's working perfectly. I'm no advanced user in excel and admit it, I'll have to play with Lookup more often I guess lol

Excellent. The LOOKUP will make your formula a lot shorter.

If your initial query is resolved, please mark your thread as Solved by clicking on Thread Tools above your initial post and click on Mark This Thread as Solved.