Solution for more Nested IF

[VKR]

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

[zbor]

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

Adjust your ranges as you need.

[VKR]

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

Adjust your ranges as you need.

This is not working.
I have attached a file for reference. So that you can understand my problem more correctly.
I wants to get the latest month having demand grater then zero in Last Demand Month (Sheet 2) from Demand (Sheet 1). In attached sheet I have fill first cell by desired result (Feb 2010). This attachment is just an example, original sheet contain very large data.

[zbor]

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 ""

[zbor]

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)

[VKR]

Appreciate your efforts..But this formula not providing correct result.Check attachment.

[martindwilson]

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

[VKR]

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

and put the value you want to check in a2

ohh my mistake...
its relay great ...
thanks boss...excel forum team is doing really amazing job...