# Formula Dependant On Another Cell



## Bedford (Monday at 4:01 PM)

I have a formula; =IF(D18<1730,1,IF(D18<3460,2,IF(D18<6921,3,IF(D18<8651,4,IF(D18<9001,4,"over 9001"))))) that is working well, however, I only need it to return a result if cell "J7" contains the word "Yes", if J7 contains the word "No", then I would like it to result in "0".
Any help would be appreciated.
Thank you.


----------



## Z51 (Monday at 4:06 PM)

This should work...

```
=IF(J7="Yes",LOOKUP(D18,{0,1730,3460,6921,8651,9001},{1,2,3,4,5,"over 9001"}),0)
```
Note: You have a return value of 4 for both <8651 and <9001, so I adjusted the second one to return 5.  Change that to a 4 if you need to.  Also, if D18 can go below zero, set the zero value in the LOOKUP formula to some negative number below anything that could show up there, e.g. "LOOKUP(D18,{-99999,1730,3460...".


----------



## Bedford (Monday at 5:49 PM)

Z51 said:


> This should work...
> 
> ```
> =IF(J7="Yes",LOOKUP(D18,{0,1730,3460,6921,8651,9001},{1,2,3,4,5,"over 9001"}),0)
> ...


Hi, I've yet to put this into action, before doing so, can I ask if it needs to be an array? On a separate note, thank you for being on top of the adjustment for <9001 to 5, I also need it to add a 6th, but I'm stuck with that extension of the formula as well. The part of the equipment is in lengths in multiples of 1730 millimeter's, so at 8650 it requires 5 lengths, at 9000 which is the maximum, it needs to calculate for 6 pieces?


----------



## Z51 (Monday at 6:05 PM)

It doesn't need to be array-entered, it just contains two arrays as part of the lookup function.

As for the ranges, the lookup function is looking at the value in D18 and then comparing it to the values in the first array:  0, 1730, 3460, etc.  Each value in the first array (must be in ascending order) is the lower limit of a range, e.g. 0-1729, 1730-3459, 3460-6920, etc.  Each of those values corresponds to a return value in the second array: 1, 2, 3, etc.

So if D18 is 3500, that value falls into the 3rd "range" from the first array and will return the third value from the second array (3, in this case).  So in the formula I provided, if D18 is 9001+, the result will be "over 9001" as a literal text string.  Change "over 9001" to 6 if you want the result to be 6.

Lookup functions can be used in this way (using numeric lower limits for ranges) or even a direct one-to-one lookup, e.g.:

```
=LOOKUP(A1,{"April","August","December","February","January","July","June","March","May","November","October","September"},{4,8,12,2,1,7,6,3,5,11,10,9})
```
This could be used to return the month # from a cell containing the text name of the month.  (There are easier ways to do this, but just an example.)  Notice the first array is in ascending alphabetical order, and the second array has return values for each position in the first array.

Hopefully that helps!


----------



## Bedford (Monday at 8:14 PM)

Z51 said:


> It doesn't need to be array-entered, it just contains two arrays as part of the lookup function.
> 
> As for the ranges, the lookup function is looking at the value in D18 and then comparing it to the values in the first array:  0, 1730, 3460, etc.  Each value in the first array (must be in ascending order) is the lower limit of a range, e.g. 0-1729, 1730-3459, 3460-6920, etc.  Each of those values corresponds to a return value in the second array: 1, 2, 3, etc.
> 
> ...


I think I get it now. So I modified it to read; =IF(J7="Yes",LOOKUP(D18,{0,1,1730,3460,6921,8651,9001},{1,2,3,4,5,6,"over 9001"}),0). Now if <=8650 it returns 5, if =>8651 it returns 6, and >9000 it returns "over 9001".
Thank you for your help!


----------



## Peter_SSs (Monday at 8:43 PM)

Bedford said:


> So I modified it to read; =IF(J7="Yes",LOOKUP(D18,{0,1,1730,3460,6921,8651,9001},{1,2,3,4,5,6,"over 9001"}),0). Now if <=8650 it returns 5, if =>8651 it returns 6, and >9000 it returns "over 9001".


  But is that doing what you want? 
It returns 2 if D18 = 1500 whereas your original formula would return 1 for that input.

Also, you talked about multiples of 1730 yet this formula is quite haphazard in that regard. That first array contains
0 = 1730*0
1 = 1730*0*+1*
1730 = 1730*1
3460 = 1730*2
(5190 = 1730*3 is completely missing)
6921 = 1730*4*+1*
8651 = 1730*5*+1*

Why is the blue one missing?
Why do some of the multiples have 1 added and some do not?


----------



## Bedford (Monday at 8:50 PM)

Peter_SSs said:


> But is that doing what you want?
> It returns 2 if D18 = 1500 whereas your original formula would return 1 for that input.
> 
> Also, you talked about multiples of 1730 yet this formula is quite haphazard in that regard. That first array contains
> ...


You're correct, it is returning 2 at 1500. The original formula I was struggling with getting it to return 6 when >=8651? And indeed 5190 is missing, confused...


----------



## Bedford (Monday at 8:53 PM)

Peter_SSs said:


> But is that doing what you want?
> It returns 2 if D18 = 1500 whereas your original formula would return 1 for that input.
> 
> Also, you talked about multiples of 1730 yet this formula is quite haphazard in that regard. That first array contains
> ...


I think this is now working; =IF(J7="Yes",LOOKUP(D18,{0,1730,3460,5190,6921,8651,9001},{1,2,3,4,5,6,"over 9001"}),0)


----------



## Peter_SSs (Monday at 9:06 PM)

This question still remains:


Peter_SSs said:


> Why do some of the multiples have 1 added and some do not?


----------



## Peter_SSs (Monday at 9:12 PM)

I am asking partly because it simply seems inconsistent but also for an input of 6920 this latest formula returns 4 whereas the original formula returns 3.


----------



## Bedford (Monday at 4:01 PM)

I have a formula; =IF(D18<1730,1,IF(D18<3460,2,IF(D18<6921,3,IF(D18<8651,4,IF(D18<9001,4,"over 9001"))))) that is working well, however, I only need it to return a result if cell "J7" contains the word "Yes", if J7 contains the word "No", then I would like it to result in "0".
Any help would be appreciated.
Thank you.


----------



## Bedford (Monday at 9:14 PM)

Peter_SSs said:


> This question still remains:


They in fact all should have a 1 as it should have them move to the next highest number after it surpasses a multiple of 1730, I'll correct that, thanks for catching it, getting late, a little worse for wear.


----------



## Bedford (Monday at 9:16 PM)

Peter_SSs said:


> I am asking partly because it simply seems inconsistent but also for an input of 6920 this latest formula returns 4 whereas the original formula returns 3.


Peter, thank you for your patience with me, I apologize I'm burnt out and need to reconnect in the morning, I hope we can continue at another time.


----------



## Peter_SSs (Monday at 9:40 PM)

Bedford said:


> I hope we can continue at another time.


Sure. In the interim, I'm taking something of a guess at an alternative approach.

23 01 10.xlsmDEJ7Yes817185001191730220346032151904226920523865062410380over2517312261729127691942817300over294270330113134603LookupCell FormulasRangeFormulaE18:E31E18=LET(s,SEQUENCE(6),IF(J$7="Yes",IF(D18>9000,"over",XLOOKUP(D18,1730*(s-1),s,,-1)),0))


----------



## Bedford (Tuesday at 5:16 AM)

Peter_SSs said:


> Sure. In the interim, I'm taking something of a guess at an alternative approach.
> 
> 23 01 10.xlsmDEJ7Yes817185001191730220346032151904226920523865062410380over2517312261729127691942817300over294270330113134603LookupCell FormulasRangeFormulaE18:E31E18=LET(s,SEQUENCE(6),IF(J$7="Yes",IF(D18>9000,"over",XLOOKUP(D18,1730*(s-1),s,,-1)),0))


Good morning Peter. Well this is quite a twist in an alternate approach. I'm merely a novice with Excel, and as such when I woke to see your handy work I was confused not having seen such a formula, =LET(s,SEQUENCE..., very interesting, very effective, genius. 
I've also modified your formula to read; =LET(s,SEQUENCE(3),IF(J$7="Yes",IF(D18>4500,"over",XLOOKUP(D18,1730*(s-1),s,,-1)),0)), this is for a similar purpose but on a slightly smaller scale, and it works fabulously.
Thank you very much!
Doug.


----------



## Peter_SSs (Tuesday at 5:20 AM)

You're welcome. Thanks for the follow-up.


----------



## Bedford (Tuesday at 5:20 AM)

Peter_SSs said:


> You're welcome. Thanks for the follow-up.


My pleasure.


----------

