# Return column of far most right value in a row

#### Cameltoe

##### Board Regular
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
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
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
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 )

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
Use of substitute might be better than left, it would allow the formula to work beyond column Z if needed.

#### KOKOSEK

##### Board Regular
Sure but OP definitely declared range A-Z

#### Cameltoe

##### Board Regular
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
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.

##### MrExcel MVP
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
@Aladin - it was not question about value from far most right column. It was a question about far most right column.

Last edited: