"istext" behaviour/recognition

mew,

was workin' on a text recognition question with someone here:
http://www.excelforum.com/excel-new-...ml#post2071064

and another user posted this:

Please Login or Register  to view this content.

Now out of curiosity, checked it out. and it recognizes text alright... but only up to the letter "i".... zomg?

Anyone have a minute to fill me in on this magical function?

Hi Mew,

That formula isn't really finding text. It's simply finding the closest value to the lookup value that is not larger than the lookup value, per Excel Help:

The LOOKUP function array form syntax has these arguments:

lookup_value Required. A value that LOOKUP searches for in an array. The lookup_value argument can be a number, text, a logical value, or a name or reference that refers to a value.

If LOOKUP can't find the value of lookup_value, it uses the largest value in the array that is less than or equal to lookup_value.

If the value of lookup_value is smaller than the smallest value in the first row or column (depending on the array dimensions), LOOKUP returns the #N/A error value.

This is why it finds a cell with any letter starting with a through i. Note, though, that it would result in an error if a cell contained "istrain" because that is "larger" than "istext"

Using your posted workbook....
This formula returns the last text entry in the range:

Please Login or Register  to view this content.

Does that help?

Bob has a good tutorial on finding last values: http://www.xldynamic.com/source/xld.LastValue.html

You may also find this thread of interest: http://www.mrexcel.com/forum/showthread.php?t=361640

Aladin Akyurek also demos graphically how the Binary Search algorithm is employed: http://www.mrexcel.com/forum/showthread.php?t=310278

Cool
Thanks y'all.