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

 

[joeu2004]

If you know the row number corresponding to the product (e.g. row 2) and the last data is always text:

=IFERROR(LOOKUP("zzzz",B2:Z2), "")

Enter enough z's so that the string is larger than any possible text data. I suspect that even two z's is sufficient.

If the last data might be numeric or text, you might consider the following:

=IFERROR(INDEX(B2:Z2, MAX(IFERROR(MATCH("zzzz",B2:Z2),0), IFERROR(MATCH(1E+300,B2:Z2),0))), "")

1E+300 is probably larger than any possible actual numeric data. Even though it is not the largest possible numeric value, it is easy to remember. (So is 1E+100, which is also probably large enough.)

-----

Some people needlessly write 9.99999999999999E+307. That is the largest numeric value that we can enter manually. But it, too, is not the largest possible numeric value that might be calculated.

The largest possible numeric value is 1.7976931348623157E+308, which we can enter accurately in VBA. In Excel, we can enter =10*1.79769313486231E+307 + 5.79E+293.

Both are just a little silly, IMHO.

 

[King_Louie]

The formula is slightly working. I need to include an offset(?) function into the formula because it's always pulling the data in the last cell but I need data from before it. For example, the s/s has the following columns: "Status #1 Area", "Status #1 Date" , "Status #2 Area", "Status #2 Date" , etc., and then in the last two columns "Current Status Date" & "Current Status Area". With the above formula, it's always inserting the last known Area data.

+++++++++++++++++++++++++++++++
Are there any other formulas that can be used without using placeholder variables ("zzz")?

Such as, look in columns C,E,G and return the last cell with data in it?
=IFERROR(INDEX({C,E,G}, MAX(IFERROR(MATCH({C,E,G}),0), IFERROR(MATCH({C,E,G}),0))), "")

 

[sheetspread]

This gives you the cell directly left of the last fill:

Book1
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
3	HAA1	LLG1	JBB1	EGB1	SCI1	UVF1	AYT1	JPQ1	NKO1	HIY1	CYS1	KHR1	GGE1	MMQ1	BYL1	GAU1	FLT1	AWI1		FLT1
4	GNQ1	CVL1	JMM1	PQC1	WCY1	ASE1	GCU1	IFO1	AQS1	QRQ1	UUB1	MIX1	FW1	HOV1	BXU1	VG1	BTZ1	DQD1	MEC1	DQD1
5	FRD1	SZJ1	EGW1	GL1	RDO1	CYJ1	BDH1	FAP1	TMG1	ODN1	QCO1	DIW1	JNR1	MAG1	EUD1	QXK1	FUM1			QXK1
6	MQN1	KTA1	QSL1	DAV1	VOP1	BUA1	UZ1	KSV1	NAU1	TYU1	AJN1	LDU1	CDJ1	LOK1	LZ1	PZQ1	BQO1	EXZ1	UP1	EXZ1
7	RGN1	HS1	OCU1	FRM1	LZR1	PIE1	RDU1	FPT1	EBF1	QCO1	DZH1	DAJ1	CDY1	CFZ1	BNW1	LXU1	FLC1	OGO1	FLH1	OGO1
8	EDB1	BQR1	HRX1	MHJ1	PI1	NLA1	GSG1	OZP1	DZX1	HCT1	GM1	JVE1	OWA1							JVE1
9	INY1	HTT1	RYB1	EGP1	WAS1	MJL1	VXX1	MOP1	JAF1	MPL1	KCI1	FNM1	AWV1	PHD1	NFT1	UHK1	UXO1	QAD1	CEU1	QAD1
10	VEH1	RMR1	GX1	UTR1	SK1	FQM1	TUL1	MOG1	GXF1	FUG1	BPZ1	KQK1	UYK1	QZU1	OTS1	IWB1	WNB1	BP1	WJI1	BP1
11	HRH1	KAL1	OAO1	JXC1	LQN1	NBC1	TYI1													NBC1
Sheet1

Cell Formulas
Range	Formula
W3	W3	=LOOKUP(2,1/($D3:$V3<>""),$C3:$U3)

 

[King_Louie]

Thanks for the help so far but still not working correctly. Looking to populate the "Current Status Date" & Current Status Area" columns with the last known status updates for each row. For example for Car # AA, is currently at QC (Quality Control) as of the 10/25/19 and BB22 is at Paint as of 10/15/19. These last known statuses (Area & Date) should be populated in "Current Status Date" & Current Status Area" .

Car #​	Status #1 Date	Status # 1 Area	Status #2 Date	Status # 2 Area	Status #3 Date	Status # 3 Area	Current Status Date	Current Status Area
AA11​	10/1/2019​	assembly	10/15/2019​	paint	10/25/2019​	QC	10/25/2019	QC
BB22​	10/1/2019​	assembly	10/15/2019​	paint			10/15/2019	paint
CC33​	10/1/2019​	assembly					10/1/2019	assembly

 

[sheetspread]

This?

Book1
	A	B	C	D	E	F	G	H	I
1	Car #	Status #1 Date	Status # 1 Area	Status #2 Date	Status # 2 Area	Status #3 Date	Status # 3 Area	Current Status Date	Current Status Area
2	AA11	10/1/2019	assembly	10/15/2019	paint	10/25/2019	QC	10/25/2019	QC
3	BB22	10/1/2019	assembly	10/15/2019	paint			10/15/2019	paint
4	CC33	10/1/2019	assembly					10/1/2019	assembly
Sheet2

Cell Formulas
Range	Formula
H2	H2	=LOOKUP(2,1/($B2:$G2<>""),$A2:$F2)
I2	I2	=LOOKUP(2,1/($B2:$G2<>""),$B2:$G2)

 

[joeu2004]

The formula is slightly working. [....] it's always pulling the data in the last cell but I need data from before it.

Well, you did write initially: ``from the last column containing data so I can see the current status``. Now you are saying that you want the current status date?

How about:

=IFERROR(INDEX(B2:Z2, MATCH("zzzz",B2:Z2)-1), "")
or
=IFERROR(INDEX(B2:Z2, MAX(IFERROR(MATCH("zzzz",B2:Z2)-1,0), IFERROR(MATCH(1E+300,B2:Z2)-1,0))), "")

formatted as Date or some date variant.

I see nothing wrong with using "zzzz" or 1E+300. "That's how it's done". But CHAR(255) probably works instead of "zzzz". "The test is left as an exercise for the student".

I also see no reason to use the second formula if "status area" is always text. Even if it looks numeric, it would be prudent to enter it as text (or format the column as Text beforehand), IMHO. KISS.

Clarification: The search type ("zzzz") depends on the type of the last column, not the column that you are selecting (numeric date). You do not need the second formula just because the result of the formula is numeric (date).

 

[King_Louie]

Ran into issue to an unforeseen issue. The above example did not consider blank cells in a current status grouping. For example, please see revised table below. The most recent status for Car #AA11 is in Status # 3. The suggested formulas above (based on my initial request) would start at Status #3 Area because that is the first cell with data/text--throwing off returned data/text. In my actual spreadsheet, there are numerous fields within each status group that may be blank.

The Date section in each status group (1,2,&3) will be a mandatory entry field. What formula would be best to look in the Columns B,E,&H to find the most recent entry and then input data based on specified offset (+1,+2,+3)?

A

B

C

D

E

F

G

H

I

J

K

L

M

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

AA11

10/1/2019

assembly

10/15/2019

paint

blue

10/25/2019

QC

10/25/2019

QC

BB22

10/1/2019

assembly

broken light

10/15/2019

paint

red

10/15/2019

paint

red

CC33

10/1/2019

assembly

10/1/2019

assembly

 

[Aladin Akyurek]

if you are after the last numeric value, just invoke:

=LOOKUP(9.99999999999999E+307,Reference)

where reference is a whole column or a whole row.

 

[King_Louie]

if you are after the last numeric value, just invoke:

=LOOKUP(9.99999999999999E+307,Reference)

where reference is a whole column or a whole row.

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