Trick to use Sumproduct with Text?

Hello Dathan, I don't think you'd want SUMPRODUCT for this...

try

=LOOKUP(REPT("z",255),A2:A10)

If your only tool is a hammer, all your problems look like nails, eh? I guess I am over-zealous with the sumproduct function.

Barry, your solution worked perfect. thank you!!

I don't understand why it works though, can you (or anyone if Barry's not online) step me through what it does?

The formula actually returns the last text value in the range. As you only have one text value in A2:A10 then the last one will always be the only one.

If you have text in A2 and text in A7 and a number in A8 it will return the text in A7

The lookup value is REPT("z",255) which is 255 "z"s - this is the "largest" possible text value in excel.

With LOOKUP the formula should return the greatest value less than the lookup value (of the same data type, text or number) in the lookup range (A2:A10). Because no text value can be greater than REPT("z",255) the formula should return the "largest" text value in the range.....but there's another twist.....

...when using LOOKUP the lookup range should be sorted ascending, so the largest value should always be the last one. The lookup range may not actuallt be sorted, but because excel assumes it will be it returns the last text value in the range.

Note: Similarly you can extract the last numeric value with:

=LOOKUP(9.99999999999999E+307,A2:A10)