How to find what cell containsvalue and get another value in the same column

[a94andwi]

Hello.

I have an order sheet designed this way:

Column A: Customer
Column B: Part number
Column C: Order number
Column D - AF: Delivery week for the ordernumber.

On each row the part number quantity is placed in the week column.

Please see the attached workbook

How do I make an offset function that finds the delivery week for each order based in the quantity data on each row
See the workbook for more info.
Bok13.xlsx

[sktneer]

You may try something like this....

In L3

=IF(COUNTA(D3:K3)>0,INDEX($D$2:$K$2,MATCH(TRUE,INDEX(D3:K3<>"",),0)),"")

and copy down.

[Pepe Le Mokko]

See attached

[sktneer]

I think Pepe was lazy enough to type the suggested formula in his post itself and uploaded a workbook, so on his behalf I am typing his suggested formula here for members who don't want to open an attachment but still want to see the another approach to achieve the desired output...

Pepe's Formula:

=INDEX($D$2:$K$2,(SUMPRODUCT(--($D3:$K3<>""),COLUMN($A3:$H3))))

[a94andwi]

It works but now I am curious. Why the column A3:H3 at the end. Why not A3:K3?

[samba_ravi]

=LOOKUP(2,1/D3:L3,D$2:L$2)
Try this and copy towards down

[Pepe Le Mokko]

A3:H3 serves as a counter and returns an array 1,2,3,...8 which then gives the position in the D2:K2 row

Also A:K would not work with sumproduct as the arrays have to contain the same number of elements

[a94andwi]

Perfect. I understand! Thanks for your help

[samba_ravi]

Is post no 5 not worked?