Formula to show actual state of the contract

[Thediik]

Hello,

I have a formula question about a excel. In my new sheet i plan to track my spare part contracts.

In columns "N" i want to express the stadium in which the project is, as you could see in attached photo.

Instead of writing the state manually i want formula to do it for me. I looked up for this topic here Formula to find the value of the closest non-blank cell to the left

but hat didnt help.

Is there a way how i can write this formula?

Thank you
Thediik

 

[SanjayGMusafir]

Is there a way how i can write this formula?

In cell N2 Try and revert -

=IFS($K2<>"","Expedition",$J2<>"","Manufacture",$I2<>"","Purchase",True,"")

OR

To make it more dynamic

=IFS($K2<>"",$K$1,$J2<>"",$J$1,$I2<>"",$I$1,True,"")

 

[Herakles]

There is nearly always a way.

Try this in N2 and copy it down.

=OFFSET($A$1,0,MATCH(LOOKUP(2,1/($A2:$M2<>""),$A2:$M2),$A2:$M2,0)-1)

 

[Thediik]

There is nearly always a way.

Try this in N2 and copy it down.

=OFFSET($A$1,0,MATCH(LOOKUP(2,1/($A2:$M2<>""),$A2:$M2),$A2:$M2,0)-1)

Thank you so much it works perfectly.

 

[Peter_SSs]

I would avoid the volatile function OFFSET and try this anyway simpler formula.

BTW, I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

davez.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Date	State 1	State 2	State 3	State 4	State 5	State 6	State 7	State 8	State 9	State 10	State 11	State 12	Actual State
2	24/07/2023								Ordered					State 8
3	24/07/2023								Ordered	Assembly				State 9
4	24/07/2023
5	24/07/2023	abc												State 1
Sheet1

Cell Formulas
Range	Formula
N2:N5	N2	=IFNA(LOOKUP("zzz",B2:M2,B$1:M$1),"")