Thread: Solution for more Nested IF

Hi,

I have work sheet that contain DEMAND in numbers for 36 months in columns corresponding to 40000 PARTS in rows. I have another sheet that have 6000 PARTS. I wants to get latest month that have demand from first sheet corresponding to each PART of 2nd sheet. I am using formula
=IF(BW3>0,$BW$2,IF(BV3>0,$BV$2,IF(BU3>0,$BU$2)))
but this can be use upto 8 months only. Please solve my problem so that i will get desired result for 36 months in minimum time.

Its my first query ...

Try =LOOKUP(2, 1/(BW3:CW3>0), BW2:Cw2)
Hope it will work.

Adjust your ranges as you need.

Here, try this:

=LOOKUP(2, 1/(Demand!B2:AL2<>0), Demand!$B$1:$AL$1)

and pull down

If you want to include 0 (in case it's not same as blank like april.10 in first example rather than feb.10)

then just change 0 to ""

Above solution is for any entry (even negative). For just positive values use:

=LOOKUP(2, 1/(Demand!B2:AL2>0), Demand!$B$1:$AL$1)

you arn't using it right see example
btw a10 is november 11
or you could use

=LOOKUP(2, 1/(INDIRECT("demand!"&ADDRESS(MATCH(A2,Demand!$A$1:$A$21,0),2,,1)&":"&ADDRESS(MATCH(A2,Demand!$A$1:$A$ 21,0),38,,1))>0), Demand!$B$1:$AL$1)

and put the value you want to check in a2