In article ,
says...
? Otherwise it's your preferred undocumented behavior against someone else's
undocumented behavior.


Oh, really?

HLOOKUP, VLOOKUP, LOOKUP return incorrect values in Excel
http://support.microsoft.com/default...b;en-us;181201

There's a similar article about MATCH.

MS has also documented a list of XL functions that accept arrays as
arguments. Of course, given how incompatible MS's search algorithms
and my way of thinking are I cannot find it after 30 minutes of
searching. But, I believe N() is on that list.

The bottom line is this. All of you who enjoy exploiting *documented*
bugs in XL are welcome to do so. If -- and yes I know all about pigs
not yet becoming airbore -- MS fixes those holes in its software you
will have no one to blame but yourself. However, hoisting those
"solutions" onto others *without* warning about what you are doing is
what I object to.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
"Tushar Mehta" wrote...
Consider the simpler array formula
=MID(A1,MAX(ROW(INDIRECT("1:"&LEN(A1)))
*N(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)=","))+1, 1024)
...

Gosh, why not consider doing it manually?

Where to begin deconstructing this? Let's start with the inconsistency of
recommending against using undocumented lookup/match functionality but
recmmending using N()'s undocumented behavior when fed arrays of booleans.
Next there's explicit use of ROW(INDIRECT("1:"&LEN(A1))) rather than using a
defined name with a fixed upper bound, but (what the heck) using an
arbitrarily large 3rd argment to the outer MID call.

If you want to use only explicitly documented functionality of built-in
functions only, shouldn't you use

=RIGHT(A1,LEN(A1)-MAX(IF(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)=",",
ROW(INDIRECT("1:"&LEN(A1))))))

? Otherwise it's your preferred undocumented behavior against someone else's
undocumented behavior.