Error in ISNA formula

[cjoly]

Greetings from France,

I would appreciate it if you could have a look at the following formula and tell me what is wrong as I get a message indicating that there is an error in the formula
=IF(ISNA(VLOOKUP(B3;COMPANY2;15;TRUE));"";VLOOKUP
(B3;COMPANY2;15;TRUE))

IN FACT I AM USING A FRENCH VERSION OF EXCEL AND THE ORIGINAL FORMULA IS

=SI(ESTNA(RECHERCHEV(B3;COMPANY2;15;VRAI));"";RECHERCHEV
(B3;COMPANY2;15;VRAI))

Thank you for your help and best regards from Paris,

Christophe

 

[cjoly]

Aladin:

Thank you for your great input. It does work. You were correct in assuming that COMPANY2 is the defined name that refers to a data area I want to look in.

My only concern now is that I would like so much to understand what you made me do but I am afraid this is a lost cause.

Tanks you again for your help.

Christophe

 

[cjoly]

Sorry,

I thought my answer had not been posted but now I have ssen the second page.

Thanks.
This message was edited by cjoly on 2002-11-13 07:24

 

[Aladin Akyurek]

[...]My only concern now is that I would like so much to understand what you made me do [...]

Christophe,

The formula in D3

=SI(NBCAR(B3);RECHERCHEV(B3;COMPANY2;15;0);"")

first checks whether there is any value in B3 -- NBCAR computes the length of a value, e.g., NBCAR(B3) is 0 when there is nothing in B3. Thus, if the condition part of IF is 0, which Excel interprets as FALSE, then we get "" in D3, otherwise the VLOOKUP bit is activated and a retrieval result is computed.

The formula in C3

=SI(ESTNA(D3);"";D3)

checks whether D3 contains an #N/A with ESTNA -- if D3 houses #N/A as result, we get "" in C3, otherwise the value of D3 that is the result of a successful retrieval.

Aladin

 

[cjoly]

Aladin:

Thank you for your kind explanation. You made it easy for me. From what I understood I put the formula in C3 instead of D3

=SI(NBCAR(B3);RECHERCHEV(B3;COMPANY2;15;0);"")

and this way I don't need the other formula (the ESTA one). It is simpler as this does not create an additional colum and work perfect.

Do you seen any glitches that I would have missed.

Thanks again for your graet assiste.

Best rgards from France,

Christophe

 

[Aladin Akyurek]

On 2002-11-13 17:24, cjoly wrote:


Aladin:

Thank you for your kind explanation. You made it easy for me. From what I understood I put the formula in C3 instead of D3

=SI(NBCAR(B3);RECHERCHEV(B3;COMPANY2;15;0);"")

and this way I don't need the other formula (the ESTA one). It is simpler as this does not create an additional colum and work perfect.

Do you seen any glitches that I would have missed.

Thanks again for your graet assiste.

Best rgards from France,

Christophe

Yes. With that change, you cannot suppress seeing #N/A's in the result column. In the previous setup, you could simply hide column D.

There is another solution which does not require an additional column. If you install the Morefun.xll add-in (the French version) of Laurent Longre, which is downloadable from

http://longre.free.fr/pages/telecharge/index.html

you can have the following single formula instead:

=SI(NBCAR(B3);SI(ESTNA(DVAR(RECHERCHEV(B3;COMPANY2;15;0)));"";LVAR());"")
This message was edited by Aladin Akyurek on 2002-11-13 17:56

 

[cjoly]

Aladin:

In fact it did suppressed the #NA's in my column C as they were the result of no information in the corresponding cell of column B. However, I thought I will get an #NA when the corresponding cell in column 15 of range named COMPANY2 is empty but in fact I get a 0 (zero) which is ok with me.

Thank you again and I won't need Laurent Longre who is by the way the very first guy who help me on an Excel forum about a month ago.

Christophe