Nested IF statement using Lookup is not working

I have a "table" on a separate worksheet & set it up to use a lookup formula (rather than a vlookup formula) since the lookup vector is one column (not the first one in the table) and the result vector is one column (which is the first one in the table), and that was clearer visually. I had originally set the table up with ranges (e.g. >8<=16, >16<=24, etc.), but found lookup doesn't like that and will only search for the greatest comparison value that is less than or equal to the lookup value. In addition, I will need to add several lookup value results together and will need to be able to copy them down with the rows relatively adjusting in column C of the comparison & formula sheet. (The table is on a separate sheet.)

After attempting to adjust my table accordingly, I realized I had to deal with a situation where a result was less than 3. Anything less than 3 I want the result to be zero, but if my comparison is equal to 3, I want the result to be 1. Therein was a challenge.

I considered doing a false statement to return an exact match, but that will only be useful if the comparison is exactly 3 (unlikely, but possible). All the rest of my values can't use an exact match.

I came up with a nested IF statement using lookup as follows, but it isn't getting past the logical test to return the value if false portion, and I don't know if it's the syntax & use of brackets, or my logic, or my table:

=IF(($C2<3),0,LOOKUP($C2,DayTable!$B$2:$B$21,$A$2:$A$21))+IF(($C3<3),0,LOOKUP($C3,DayTable!$B$2:$B$21,$A$2:$A$21))+IF(($C4<3),0,LOOKUP($C4,DayTable!$B$2:$B$21,$A$2:$A$21))

Alternatively, Excel suggested the following formula, but the results were the same and not returning the correct results:

=IF($C4<3,0,LOOKUP($C4,DayTable!$B$2:$B$21,$A$2:$A$23)+IF($C5<3,0,LOOKUP($C5,DayTable!$B$2:$B$21,$A$2:$A$23)+IF($C6<3,0,LOOKUP($C6,DayTable!$B$2:$B$21,$A$2:$A$23))))

I can attach a sample spreadsheet if that would be helpful, but I would need to adjust it to protect confidentiality. I'm a bit perplexed as to why this isn't working or how to achieve the desired results. Any suggestions would be welcome!

Originally Posted by daddylonglegs

For lookup to work correctly the "lookup range", in this case DayTable!$B$2:$B$21 needs to be sorted ascending, is that the case?

Yes, the "lookup range" is sorted in ascending order. I will put together a sample workbook & attach it shortly...

I have attached a sample file. Sheet1, column G (in yellow) is where I have been trying different formulas, without getting them to work. The numbers shown in that column have been manually calculated and are correct, except G5, which should be 0. (So far the formulas I've tried only return a 0 or a #N/A error.)

DayTable1 shows the correct ranges to lookup, and DayTable is the adjusted table to work with the Lookup formulas, if I did that correctly. Column B is listed in ascending order. Because column C is negative numbers, I'm not sure if they are actually in ascending order or not? I will be doing similar formulas in column H of Sheet 1 for column C of DayTable and looking for results like that in DayTable1, which should match the manually calculated numbers already on Sheet1. (I won't need a lookup formula for column D and will just incorporate the one scenario in the formula.)

Once those formulas are built and working they will be the same but cumulative for the other extended columns in Sheet1.

As you can see from column C in Sheet1, the numbers are unpredictable, are both positive and negative out to 3 digits, and will constantly change as new numbers are added daily, so I can't actually list all of the numbers in a lookup formula.

Any help would be great!

Originally Posted by martindwilson

i think we need to see whats in DayTable!$B$2:$B$21,$A$2:$A$21,and that is not a nested if ,that just sums 3 different look up results
=IF($C2<3,0,LOOKUP($C2,DayTable!$B$2:$B$21,$A$2:$A$21)) is the correct way for the first component
buy the lookup could just be LOOKUP($C2,DayTable!$B$2:$B$21,$A$2:$A$21)) as long as the lookup table starts with a 0

=LOOKUP(C2,{0,3,6,8,9},{0,100,200,300,400}) anything less than 3 gives 0

I could add a line in my table for 0 with 2.999 in column B. That way less than 3 would result in 0, and equal to 3 should give a result of 1. Would that be easier?

Originally Posted by martindwilson

ok back to the if and a bit of rounding up and remove the 0 from the table
=IF(G1<3,0,LOOKUP(ROUNDUP(G1,0),$E$2:$E$21,$F$2:$F$21)) see attached you can see the test results on the right

Removing the 0 from the table and using your formulas worked great in the test area, but when I tried it in my actual spreadsheet I got either 0's or #N/A errors. I have no idea why that would be the case.

I used essentially your formula and your table in my spreadsheet. I considered maybe it was because my comparison values had links to another spreadsheet, but opening that spreadsheet didn't help, and neither did using values instead of the links.

I am attaching your spreadsheet where I have added a section at the bottom of DayTable that uses your formula but adds 3 IF statements to see if that was the problem, but again it worked great in the test area. The example I have there is for G12 from Sheet1. The formula on Sheet1 yields 0, but the correct result of 3 is given in the test area on DayTable.

Do you know why that would be the case? I must be missing something. Is it the syntax I used when referring to DayTable? Note that DayTable is in the same spreadsheet as the formulas. I don't know what else it could be...?

I'd love your input! Thanks so much!!!

Originally Posted by martindwilson

you haven't included the sheet name in the second range e.g.
=LOOKUP(ROUNDUP($C7,0),DayTable!$E$2:$E$21,$F$2:$F$21)
should be
=LOOKUP(ROUNDUP($C7,0),DayTable!$E$2:$E$21,DayTable!$F$2:$F$21)

Oh, of course! Wow, thank you so so much!!! Your help is amazing and I can't tell you how much you made my day! Many many thanks & stars galore!

BTW, I will be working on the same thing for losses next and may have some questions there. For instance, negative numbers in ascending order look like they need to show the ranges with -153 at the top and -3 at the bottom for lookup to work properly. I'm not sure if I can use your same ranges in reverse, or if I'll need to adjust them the other way, or if I should again roundup or use rounddown. Any suggestions...?