Return column of far most right value in a row

Cameltoe

Board Regular
Joined
Jun 5, 2018
Messages
175
Hi,

In column range A-Z, row 1, there are cell values in E1, M1, Y1, the rest are blank. What functions can I use to determine the far most right column containing any value, in this case Y?
 

KOKOSEK

Board Regular
Joined
Apr 8, 2019
Messages
206
Office Version
365, 2013
Platform
Windows
If numbers:
Code:
=LEFT(ADDRESS(1,MATCH(10^9,A1:Z1,1),4,1),1)
if text:
Code:
=LEFT(ADDRESS(1,MATCH("ZZZZZZZ",A1:Z1,1),4,1),1)
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
4,984
Office Version
365
Platform
Windows
an array formula is committed using {CTRL}{SHIFT}{ENTER} and that is what generates the curly brackets
Excel 2016 (Windows) 32 bit
A
B
C
D
E
F
G
H
I
J
K
L
1
WhatFormulaResult
4
6
9
100
XXX
2
VALUE in last column in row1 =LOOKUP(2,1/(1:1<>""),1:1)XXX
3
CELL reference (array formula) {=ADDRESS(1,MAX(IF(1:1=LOOKUP(2,1/(1:1<>""),1:1),COLUMN(1:1)-MIN(COLUMN(1:1))+1)),4)}L1
4
COLUMN no (array formula) {=MAX(IF(1:1=LOOKUP(2,1/(1:1<>""),1:1),COLUMN(1:1)-MIN(COLUMN(1:1))+1))}
12​
Sheet: Sheet11

C2
=LOOKUP(2,1/(1:1<>""),1:1)

C3 (array formula )
=ADDRESS(1,MAX(IF(1:1=LOOKUP(2,1/(1:1<>""),1:1),COLUMN(1:1)-MIN(COLUMN(1:1))+1)),4)

C4 ( array formula )
=MAX(IF(1:1=LOOKUP(2,1/(1:1<>""),1:1),COLUMN(1:1)-MIN(COLUMN(1:1))+1))
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
7,468
Office Version
2019
Platform
Windows
Use of substitute might be better than left, it would allow the formula to work beyond column Z if needed.

=SUBSTITUTE(ADDRESS(1,MATCH(1E+100,1:1,1),4,1),ROW(1:1),"")

=SUBSTITUTE(ADDRESS(1,MATCH("zzz",1:1,1),4,1),ROW(1:1),"")
 

KOKOSEK

Board Regular
Joined
Apr 8, 2019
Messages
206
Office Version
365, 2013
Platform
Windows
Sure but OP definitely declared range A-Z :p
 

Cameltoe

Board Regular
Joined
Jun 5, 2018
Messages
175
Thank you all for input, will try it and try to integrate with the rest of the formula string. You'll hear from me if I don't manage to solve it :D
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
7,468
Office Version
2019
Platform
Windows
They also declare 'blank', given that MATCH("zzz" will see formula blanks as text, maybe Yongle is the only one of us who got it right ;)

If they are empty, then an additional formula for mixed data types.
=SUBSTITUTE(ADDRESS(1,AGGREGATE(14,6,MATCH(CHOOSE({1,2},1E+100,"zzzz"),1:1),1),4,1),1,"")
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,139
Hi,

In column range A-Z, row 1, there are cell values in E1, M1, Y1, the rest are blank. What functions can I use to determine the far most right column containing any value, in this case Y?
If A1:Z1 is numeric:

=LOOKUP(9.99999999999999E+307,A1:Z1)

If A1:Z1 is text:

=LOOKUP(REPT("z",255),A1:Z1)

If A1:Z1 may house any value including blanks:

=LOOKUP(9.99999999999999E+307,1/(A1:Z1<>""),A1:Z1)
 

KOKOSEK

Board Regular
Joined
Apr 8, 2019
Messages
206
Office Version
365, 2013
Platform
Windows
@Aladin - it was not question about value from far most right column. It was a question about far most right column.
 
Last edited:

Forum statistics

Threads
1,086,220
Messages
5,388,550
Members
402,120
Latest member
dmitrevski

Some videos you may like

This Week's Hot Topics

Top