Find value to left

[jamescooper]

This formula finds the last non-blank value in a row, how do I find the value to the left of it (same row)?

=LOOKUP(2,1/(NOT(ISBLANK($O5:$XFD5))),$O5:$XFD5)

Many thanks.

 

[awoohaw]

Try:

=INDEX($O$5:$XFD$5,MAX((ISBLANK($O$5:$XFD$5)=FALSE)*(COLUMN($O$5:$XFD$5)-COLUMN(O5)+1))-1)

Mr excel questions 63.xlsm
	O	P	Q	R	S	T	U
1
2
3	311
4
5	732	771			476	311	292
6
7
8
jamescooper

Cell Formulas
Range	Formula
O3	O3	=INDEX($O$5:$XFD$5,MAX((ISBLANK($O$5:$XFD$5)=FALSE)*(COLUMN($O$5:$XFD$5)-COLUMN(O5)+1))-1)

But, what do you want if the penultimate cell is blank? The above formula will return a zero.

This may be more easily done in 365 or 2021, Which version do you have? (You should update your profile so your version shows up on your posting button.

 

[jamescooper]

Thanks that works I don't think it will be blank but if it could be, would probably need a variation of the formula?

 

[awoohaw]

If you want to take a penultimate blank into consideration please tell me what version of excel you use, and update your profile button. The non 365/2021 version will be more difficult to create.

 

[jamescooper]

Microsoft 365

 

[Tetra201]

See if the following formula works for you (Excel 2010 and newer):

=INDEX(5:5,AGGREGATE(14,6,COLUMN($O5:$XFD5)/($O5:$XFD5<>""),2))

 

[awoohaw]

Here is a 365 solution, I'm sure there are others as well.

=LET(
nbrs,FILTER($O$5:$XFD$5,$O$5:$XFD$5<>""),
nnbrs,COUNTA(nbrs),
CHOOSECOLS(nbrs,nnbrs-1))

 

[Fluff]

Microsoft 365

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Another option

=choosecols(filter(O5:XFD5,O5:XFD5<>""),-2)