Return column of far most right value in a row

[Camel123]

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]

If numbers:

=LEFT(ADDRESS(1,MATCH(10^9,A1:Z1,1),4,1),1)

if text:

=LEFT(ADDRESS(1,MATCH("ZZZZZZZ",A1:Z1,1),4,1),1)

 

[Yongle]

an array formula is committed using {CTRL}{SHIFT}{ENTER} and that is what generates the curly brackets
Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][th]

G​

[/th][th]

H​

[/th][th]

I​

[/th][th]

J​

[/th][th]

K​

[/th][th]

L​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]What[/td][td]Formula[/td][td]Result[/td][td]

4​

[/td][td]

6​

[/td][td]

9​

[/td][td][/td][td]

100​

[/td][td][/td][td][/td][td][/td][td=bgcolor:#FFFF00]XXX[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]VALUE in last column in row1[/td][td] =LOOKUP(2,1/(1:1<>""),1:1)[/td][td]XXX[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]CELL reference (array formula)[/td][td] {=ADDRESS(1,MAX(IF(1:1=LOOKUP(2,1/(1:1<>""),1:1),COLUMN(1:1)-MIN(COLUMN(1:1))+1)),4)}[/td][td]L1[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]COLUMN no (array formula)[/td][td] {=MAX(IF(1:1=LOOKUP(2,1/(1:1<>""),1:1),COLUMN(1:1)-MIN(COLUMN(1:1))+1))}[/td][td]

12​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet11[/td][/tr][/table]

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]

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]

Sure but OP definitely declared range A-Z

 

[Camel123]

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]

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]

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]

@Aladin - it was not question about value from far most right column. It was a question about far most right column.

 

[jasonb75]

There are 2 threads running for the same question, the other one has the more recent activity. https://www.mrexcel.com/forum/excel-questions/1108125-formula-help-several-steps.html