Match

[How_Do_I]

If I use =MATCH(9.99999999999999E+307,Fixtures!$N:$N) to find the row with the last figure in ColumnN...

What do I use to find the row with the last text entry please?

 

[Gerald Higgins]

ZZZs usually work for me

by which I mean, of course, replace the 9.99....E+307 bit with "ZZZZZZ"

 

[jasonb75]

Something like

=MATCH("ZZ",Fixtures!$N:$N)

most people seem to advise using REPT("Z",100) or similar in place of "ZZ", but unless there is a possibility of a text entry that starts with "ZZ" there is no need to.

 

[How_Do_I]

Thanks gents... I found this in a formula Aladin gave me...

=MATCH(REPT("z",255),$N:$N). I think that repeats the "z" 255 times?

 

[Gerald Higgins]

Yes it does. But as Jasonb75 said, it's not really necessary if you know there's nothing starting with ZZ, in which case just "ZZ" will suffice.

For example, if the text values are the names of football clubs, and you know that none of them start with "Z", then just "Z" will do.

 

[How_Do_I]

Yes it does. But as Jasonb75 said, it's not really necessary if you know there's nothing starting with ZZ, in which case just "ZZ" will suffice.

For example, if the text values are the names of football clubs, and you know that none of them start with "Z", then just "Z" will do.

Thanks Gerald

 

[T. Valko]

If I use =MATCH(9.99999999999999E+307,Fixtures!$N:$N) to find the row with the last figure in ColumnN...

What do I use to find the row with the last text entry please?

For the last numeric value:

=MATCH(1E100,Fixtures!$N:$N)

For the last text value:

=MATCH("zzzzz",Fixtures!$N:$N)

 

[Aladin Akyurek]

If I use =MATCH(9.99999999999999E+307,Fixtures!$N:$N) to find the row with the last figure in ColumnN...

What do I use to find the row with the last text entry please?

=MATCH(REPT("z",255),Fixtures!$N:$N)

where REPT("z",255) produces the largest string with a lenght of 255 (Excel's text limit in a cell in earlier versions)...

It's easier to define BigNum and BigStr (by means of Insert|Name|Define or Formulas|Name Manager) as referring to:

=9.99999999999999E+307

and

=REPT("z",255)

and use these in formulas like

=MATCH(BigNum,Fixtures!$N:$N)

=MATCH(BigStr,Fixtures!$N:$N)

=LOOKUP(BigNum,Fixtures!$N:$N)

=LOOKUP(BigStr,Fixtures!$N:$N)

 

[How_Do_I]

Thanks Aladin...