ExcelBanter - View Single Post

Ron Rosenfeld

On 31 Dec 2004 01:12:42 GMT, OSPAM (JimFor) wrote:

Hi,

I would like to know what I can to do prevent this from happening. One thing I
thought I would do is to set the format of both lists of account numbers to
the same format with the same number of characters. because it would seem that
the problem lies in the account numbers but I don't know if that would help and
do not know how to do that not what format to use. ( But, as I said, sometimes
the formats appear to be same and the function still does not work.) Any
suggestion to prevent this from happening would be appreciated.

Thanks

I'm not certain I understand everything you've posted. However, it sounds as
if, in some indeterminate fashion, your account numbers (and possibly also the
values you use for a lookup key), although they always look like numbers,
occasionally are entered as text.

If that is the case, if the LookUp key is text, it will not find a Number in
the data table, even if they appear to be the same.

So if that is your problem, and if there is no way to 'force' all the entries
to be one or the other, then you could force it in the formula.

For example, if your Account Numbers are in a one column array named AcctNum
and the corresponding Account Names are in a one column array named AcctName,
then the following array formula will give you the Account Name that
corresponds to the account number in LookUp:

=INDEX(AcctName,MATCH(VALUE(LookUp),VALUE(AcctNum) ,0))

To enter an array formula, hold down <ctrl<shift while hitting <enter (after
you've typed in or pasted in the formula). XL will place braces {...} around
the formula.

--ron