index return last value horizontally

[King_Louie]

Hi,

I have a s/s that tracks the status of a product as it moves along the manufacturing process. I'm trying to find an array formula that retrieves data from the last column containing data so I can see the current status of that product. Any ideas?

Thanks

 

[Peter_SSs]

Try these. Assumes at least one status entry per row.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Car #	Status #1 Date	Status # 1 Area	Status # 1 Comment	Status #2 Date	Status # 2 Area	Status # 2 Comment	Status #3 Date	Status # 3 Area	Status # 3 Comment	Current Status Date	Current Status Area	Current Status Comment
2	AA11	1/10/2019	assembly		15/10/2019	paint	blue	25/10/2019	QC		25/10/2019	QC
3	BB22	1/10/2019	assembly	broken light	15/10/2019	paint	red				15/10/2019	paint	red
4	CC33	1/10/2019	assembly								1/10/2019	assembly
Status

Cell Formulas
Range	Formula
K2:K4	K2	=LOOKUP(9.99E+307,B2:J2)
L2:L4	L2	=INDEX(B2:J2,AGGREGATE(14,6,(COLUMN(B2:J2)-COLUMN(B2)+1)/(B2:J2=K2),1)+1)&""
M2:M4	M2	=INDEX(B2:J2,AGGREGATE(14,6,(COLUMN(B2:J2)-COLUMN(B2)+1)/(B2:J2=K2),1)+2)&""

 

[King_Louie]

Try these. Assumes at least one status entry per row.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Car #	Status #1 Date	Status # 1 Area	Status # 1 Comment	Status #2 Date	Status # 2 Area	Status # 2 Comment	Status #3 Date	Status # 3 Area	Status # 3 Comment	Current Status Date	Current Status Area	Current Status Comment
2	AA11	1/10/2019	assembly		15/10/2019	paint	blue	25/10/2019	QC		25/10/2019	QC
3	BB22	1/10/2019	assembly	broken light	15/10/2019	paint	red				15/10/2019	paint	red
4	CC33	1/10/2019	assembly								1/10/2019	assembly
Status

Cell Formulas
Range	Formula
K2:K4	K2	=LOOKUP(9.99E+307,B2:J2)
L2:L4	L2	=INDEX(B2:J2,AGGREGATE(14,6,(COLUMN(B2:J2)-COLUMN(B2)+1)/(B2:J2=K2),1)+1)&""
M2:M4	M2	=INDEX(B2:J2,AGGREGATE(14,6,(COLUMN(B2:J2)-COLUMN(B2)+1)/(B2:J2=K2),1)+2)&""

Thanks!

=LOOKUP(9.99E+307,B2:J2) -- Looks up the largest value in selected range?

Can you briefly describe how second function works? Can it be modified to pull the "Current Status Date" too?

 

[Peter_SSs]

=LOOKUP(9.99E+307,B2:J2) -- Looks up the largest value in selected range?

No, as explained by others earlier in the thread, this structure returns the last (ie furthest to the right) numeric value in the range. In your case, judging by your sample data, this will be the date of the latest (ie furthest to the right) status.

Do your status column groups get filled left to right? It appears that way from your samples.

Can you briefly describe how second function works? Can it be modified to pull the "Current Status Date" too?

I'm not sure what you are actually asking here. In what way do the results in K2:M4 of my worksheet above, not meet what you want?

 

[Aladin Akyurek]

I want to return all data/text from the recent section group. For example, in the current grouping columns (K,L,&M) for Car #AA11, the formulas should return "10/25/2019", "QC", & "(Blank)".

Are the values in K, L, and M desired values or user-entered values?

 

[King_Louie]

No, as explained by others earlier in the thread, this structure returns the last (ie furthest to the right) numeric value in the range. In your case, judging by your sample data, this will be the date of the latest (ie furthest to the right) status.

Do your status column groups get filled left to right? It appears that way from your samples.
Yes currently they get filled in left to right

I'm not sure what you are actually asking here. In what way do the results in K2:M4 of my worksheet above, not meet what you want?

Looking to understand how the second formula is computed and why it's not used for Column K (which might be answered in formula explanation).

 

[King_Louie]

Are the values in K, L, and M desired values or user-entered values?

Not sure if I understand what you mean by "desired values" but the values in K,L,&M are formula based.

 

[Peter_SSs]

Looking to understand how the second formula is computed and why it's not used for Column K (which might be answered in formula explanation).

OK, my formulas go in columns K:M and get their returned values by looking in columns B:J

The second formula is
=INDEX(B2:J2,AGGREGATE(14,6,(COLUMN(B2:J2)-COLUMN(B2)+1)/(B2:J2=K2),1)+1)&""

The blue part makes an array of numbers, counting the columns in B:J as follows
{2;3;4;5;6;7;8;9;10} - 2 +1
giving {1;2;3;4;5;6;7;8;9}

=INDEX(B2:J2,AGGREGATE(14,6,{1;2;3;4;5;6;7;8;9}/(B2:J2=K2),1)+1)&""
Blue part makes an array of True/False values depending on whether B2:K2 is the same as the value in K2
{F;F;F;F;F;F;T;F;F}

=INDEX(B2:J2,AGGREGATE(14,6,{1;2;3;4;5;6;7;8;9}/{F;F;F;F;F;F;T;F;F},1)+1)&""
Blue part divides the corresponding values. To do this Excel converts True to 1, False to 0
{#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;7;#DIV/0!;#DIV/0!}

=INDEX(B2:J2,AGGREGATE(14,6,{#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;7;#DIV/0!;#DIV/0!},1)+1)&""
AGGREGATE(15,6,....,1) looks for the largest value in the array, ignoring errors
7

=INDEX(B2:J2,7+1)&""
=INDEX(B2:J2,8)&""
=Cell I2
="QC"

The 3rd formula is identical except that it adds 2 instead of 1 where I have highlighted red above, thus returning the value from 2 cells to the right of the last date.
The &"" on the end of each formula is so the formula returns "" not 0 if the target cell is empty.

This form of formula cannot be used for column K because it uses the value in column K so would produce a circular reference.
In any case there is a much simpler formula already for column K.

I hope that I haven't misunderstood your question and the above helps.